Finding the First Day of the Year–#SQLNewBlogger

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:

2021-12-01 09_45_54-SQLQuery3.sql - ARISTOTLE_SQL2017.sandbox (ARISTOTLE_Steve (56))_ - Microsoft SQ

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.

2021-12-01 09_47_42-SQLQuery3.sql - ARISTOTLE_SQL2017.sandbox (ARISTOTLE_Steve (56))_ - Microsoft SQ

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.

2021-12-01 09_48_45-SQLQuery3.sql - ARISTOTLE_SQL2017.sandbox (ARISTOTLE_Steve (56))_ - Microsoft SQ

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.

2021-12-01 09_49_44-SQLQuery3.sql - ARISTOTLE_SQL2017.sandbox (ARISTOTLE_Steve (56))_ - Microsoft SQ

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.

2021-12-01 09_50_41-SQLQuery3.sql - ARISTOTLE_SQL2017.sandbox (ARISTOTLE_Steve (56))_ - Microsoft SQ

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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

2 Responses to Finding the First Day of the Year–#SQLNewBlogger

  1. aaronbertrand says:

    I find DATEFROMPARTS(DATEPART(YEAR,column), 1, 1) much more intuitive.

    Also I try to teach people to stay away from shorthand – why use yy when you can spell out year?

    Like

    • way0utwest says:

      I should spell out year. I’ll correct that. I haven’t tried that with DATEFROMPARTS, but that does seem more intuitive. I always struggle with the datediff 0 trick and it makes me decode the algorithm each time.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.