Tag: T-SQL

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I ran across a question on user-defined data types, which I hadn’t worked with in a long time, so I took a minute to investigate. I wrote this post about an interesting language item.

User-defined table types let you add a new type that can be used anywhere you would use a base, or normal, data type. This means if I want to have a type of US zip codes, perhaps limiting the base zip to 5 numbers, I can create a type that is limited to 5 digits.

If I want to create a new user-defined table type, I’d have thought I did something like this:

CREATE TYPE dbo.USZipCode AS VARCHAR(5)

However that doesn’t work.

The AS structure is used in many places, but not here. Instead, we use a FROM structure. This means I’d do this:

CREATE TYPE dbo.USZipCode FROM VARCHAR(5)

This gives me a type I can use in CREATE TABLE statements, stored procedures, and more. Anywhere I’d use the varchar(5), I could do this instead:

CREATE TABLE dbo.AddressTable

( AddressID INT NOT NULL

, AddressValue VARCHAR(100)

, AddressZip USZipCode

)

This let’s me use a type that is more intuitive, I guess. I don’t find these that useful in most places. In fact, it’s a little confusing. If I were a new developer, is this a 5 or 9 (or 10) digit field? Is it numeric or string? It’s not easy to determine this. I don’t find these that useful.

SQL New Blogger

I was doing other work, but I saved a bit of code and then spent about 10-15 minutes to write up this post. This one shows less about what I learned, and more about what I think.

Always good to show to a prospective interviewer.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

While working on the question from Monday, I had to do a bit of date math. I remember this blog post from Lynn Pettis, and every new year I think of it.

I decided to decode the question a bit and work through the T-SQL myself as a good exercise for explaining what happens.

Here’s the code (setup and query):

CREATE TABLE dbo.Resolution
( ResolutionDate DATETIME
, ResolutionText VARCHAR(200)
)
GO
INSERT dbo.Resolution
(
     ResolutionDate,
     ResolutionText
)
VALUES
   ('2020-01-01 0:00', 'Do not travel by airplane this year'),
   ('2021-01-01 0:00', 'Go on vacation on a plane'),
   ('2022-01-01 0:00', 'Visit a new country')
GO
SELECT ResolutionText FROM  dbo.Resolution
WHERE ResolutionDate = DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) , 0)

In this code, the final query is designed to find the first day of the current year. Here are a few examples:

How does this work? Let’s decode things.

Digging Into the Algorithm

Let’s start with a simple thing. I use a 0 for a parameter in the DATEADD and DATEDIFF. What does that mean? Well, let’s go with the YEAR() function. If I use a 0 there, I see the base year in SQL Server, which is 1900.

This doesn’t mean I can’t use other years, but this is the basis for calculations. What if I add to this? I can add one, and I see a different date.

This is the key. I’ve gone from 0 to 1901-01-01-00:00:00. Let’s see the difference from this year, well last year when I wrote this, to 0.

The result above shows me 121. Which makes sense. 1900 to 2021 is 121 years. Now, when I use the dateadd, and add 121 to 0, I get the first day, actually the first DATETIME moment, of the current year.

I get 2021-01-01 00:00:00.

I can change the GETDATE() to any date time of any year, and this code returns the first moment of that year, essentially stripping off the other parts.

SQLNewBlogger

I was working on something and used a trick I learned from someone else. I decided to write this post, which only took about 15 minutes to write. The demo was simple, and I just broke apart the code, slowly putting each section in its own SELECT and then explaining it.

This is a good example of how to structure a blog post based on some knowledge you have and use in other work. You should try this.