Voice of the DBA

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.