## 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:

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.

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