SQL Server: First and Last Day of Year, Quarter, Month and Week

With so many questions floating around calculating the first and last day of a given duration in SQL Server, here’s a post that explains the technique of calculating the first and last day of a given duration. We will cover the following:

- Calculate First and Last Day of the Year
- Calculate First and Last Day of the Quarter
- Calculate First and Last Day of a Month

First and Last Day of Year

To get the first day of the year, we are using the DATEDIFF function to determine the number of years from ‘1/1/1900’ to the current date (GETDATE). We then add this calculated number result (in this case 111) to ‘1/1/1900’, to obtain the first day of the year.

Similarly, a simple trick is adopted to get the last day of the year. We get the first date of the ‘next year’ and simply subtract one day from it. So the expression SELECT DATEADD(yy, DATEDIFF(yy,'',GETDATE()) + 1, 0) gets you the first day of the ‘next’ year. Subtracting 1 from it gives you the last day of the current year.

The same approach is adopted to find the last day of the quarter and the month.

First and Last Day of the Quarter



First and Last Day of a Month



Here are all the three queries for you to try out:

-- First and Last Day of Year
SELECT DATEADD(yy, DATEDIFF(yy, 0, GETDATE()),0)
as 'First Day of Year'
SELECT DATEADD(dd, -1, DATEADD(yy, DATEDIFF(yy,0,GETDATE()) + 1, 0)) as 'Last Day of the Year'

-- First and Last Day of Quarter
SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)
as 'First Day of Quarter'
SELECT DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) + 1, 0))
as 'Last Day of the Quarter'

-- First and Last Day on Month
SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
as 'First Day of Month'
SELECT DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))
as 'Last Day of Month'

OUTPUT

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook