Simplify Date Period Calculations in SQL Server

By: Aaron Bertrand | Comments (9) | Related: 1 | 2 | 3 | 4 | More > Dates

Problem

Many of us regularly perform date-based operations against our SQL Server data. I have touched on some date/time best practices here and how to use a calendar table – especially for non-standard business periods and holidays – here.

I still see people use very interesting and convoluted ways to determine the beginning and end of basic periods, like the current week, the current month, and the current year.

Solution

A lot of these archaic approaches pre-date SQL Server 2008, which introduced new types like date, making it easier to calculate day boundaries, and SQL Server 2012, which introduced easier date formulation functions like DATEFROMPARTS(). At some point in 2019, this function will have been introduced five full versions ago. So, with the impending removal of SQL Server 2008 and 2008 R2 from any sort of official support from Microsoft, it seems a good time to start taking advantage of the better syntax we have available.

Getting Today's Date Without Time

To start, let's talk about getting the beginning of today (stripping the time, essentially). The method I see most commonly is to take the date 0 (an implicit addition of zero days to 1900-01-01), and add to it the number of date boundaries that have passed since 1900-01-01.

DECLARE @today datetime = DATEADD(DAY, DATEDIFF(DAY,0,GETDATE()),0);

I dislike this approach because it is not intuitive – I constantly see people asking what the 0 means, for example, or why we need to wrap two date functions here just to remove the time. This isn't much better:

DECLARE @today datetime = DATEADD(DAY, DATEDIFF(DAY,'19000101',GETDATE()),'19000101');

But at least some of the mystery is gone. This is still way better and more efficient than approaches that perform conversions to and from strings, like these:

DECLARE @today datetime = CONVERT(char(10), GETDATE(), 120);
GO

DECLARE @today datetime = FORMAT(GETDATE(), 'yyyy-MM-dd');

But I think we can get even clearer. I think the following is more intuitive, since there is no working out what math and other magic is being accomplished by DATEADD/DATEDIFF:

DECLARE @today datetime = CONVERT(date, GETDATE());

This yields a datetime, but even better would be to just use the date type in the first place, which can be used for range queries against datetime columns with no penalty:

DECLARE @today date = GETDATE();

I also dislike GETDATE() in general – both because I think we should always be dealing with UTC, and also because it is proprietary. In the examples that follow, I will use SYSDATETIME() and/or SYSUTCDATETIME(), since they are based on the standard (and offer more precision when needed).

I do wish there were built-in functions that could just return a date, like SYSDATE() and SYSUTCDATE(), but that will have to be a wish list item for a future version.

Now that we have an easy, intuitive, and reliable way to get today's date without the time, it is easy to work out the beginning of a larger period, for today's date or any date really.

Getting First Day of the Month

Again, I see really awkward approaches to calculating things, like the first day of the month. Here are a couple of my favorites, given @today as already calculated above:

SELECT m1 = DATEADD(DAY,1-DATEPART(DAY,@today),@today),
       m2 = CONVERT(date,CONCAT(YEAR(@today),RIGHT('0'+RTRIM(MONTH(@today)),2),'01'));

Aren't those super ugly? You're probably thinking, "He must be making those up." Go search Stack Overflow; I can assure you that you will find those approaches or similar in many highly-upvoted and/or accepted answers.

Given the DATEFROMPARTS() function I mentioned, which returns a date given a year, month, and day, I think the following is much more concise and self-documenting:

SELECT DATEFROMPARTS(YEAR(@today), MONTH(@today), 1);

No funky math, no string concatenation, no worrying if a month needs a leading digit – just give me the first day for the year/month of the provided date.

Getting First Day of the Year

Calculating the beginning of the year is almost identical – just give me the first day of the first month for the year of the provided date:

SELECT DATEFROMPARTS(YEAR(@today), 1, 1);

Getting First Day of the Quarter

Quarter is a little more complicated. If you use traditional calendar quarters (Jan-Mar is Q1, etc.), then you can do this:

SELECT DATEFROMPARTS(YEAR(@today), MONTH(@today)-((MONTH(@today)-1)%3),1);

The math there does get a little ugly, but basically it uses a modulo to determine how many months to subtract from the given date to land at the first month of that quarter. You may in fact find the old approach more intuitive in this specific case (and if your fiscal year does not align with a calendar year, you can use the calendar table I mentioned above):

SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @today), 0);

Getting First Day of the Week

Week is also tricky, because it depends on the DATEFIRST setting. It is also not a good candidate for DATEFROMPARTS() because the beginning of the current week might not be in the same month or the same year. So this is the calculation I use, given DATEFIRST 7:

SET DATEFIRST 7;
SELECT DATEADD(DAY,1-DATEPART(WEEKDAY,@today),@today);

Dynamically Determining the End Date of a Period

Now that you can easily derive the beginning of a reporting period or range using DATEFROMPARTS(), you will typically need to also determine the end of that range. What a lot of people do is try to literally find the end of the range, so they can use BETWEEN. This is hard because what is the "end" of February? It depends on multiple things, including whether it is a leap year, the data type of the column, and the data type of the incoming parameter. In different cases you can miss an entire day or inadvertently include the whole next day (I show examples in my best practices tip). So I have been very persistent in telling people that you don't need to find the "end" of the current period; you need to find the beginning of the *next* period. Instead of BETWEEN, you use an open-ended range.

You can run a report for each of these periods for any given date as follows:

DECLARE @day date = SYSDATETIME(); -- or SYSUTCDATETIME(), or any date literal
SET DATEFIRST 7;-- traditional week (day 1 = Sunday)
DECLARE @month   date = DATEFROMPARTS(YEAR(@day), MONTH(@day), 1),
        @year    date = DATEFROMPARTS(YEAR(@day), 1, 1),
        @quarter date = DATEFROMPARTS(YEAR(@day), MONTH(@day)-((MONTH(@day)-1)%3),1),
        @week    date = DATEADD(DAY,1-DATEPART(WEEKDAY,@day),@day);
SELECT ... FROM dbo.tablename
  -- for the given day:
  WHERE DateColumn >= @day     AND DateColumn < DATEADD(DAY,     1, @day)
  -- for the given month:
  WHERE DateColumn >= @month   AND DateColumn < DATEADD(MONTH,   1, @month)
  -- for the given year:
  WHERE DateColumn >= @year    AND DateColumn < DATEADD(YEAR,    1, @year)
  -- for the given quarter:
  WHERE DateColumn >= @quarter AND DateColumn < DATEADD(QUARTER, 1, @quarter)
  -- for the given week:
  WHERE DateColumn >= @week    AND DateColumn < DATEADD(WEEK,    1, @week)

Here is an example that queries the sys.databases table to find databases created in the last quarter. You can adapt the code above to meet your specific needs for any of your tables that have date columns.

DECLARE @day date = SYSDATETIME(); -- or SYSUTCDATETIME(), or any date literal
SET DATEFIRST 7;-- traditional week (day 1 = Sunday)
DECLARE @month   date = DATEFROMPARTS(YEAR(@day), MONTH(@day), 1),
        @year    date = DATEFROMPARTS(YEAR(@day), 1, 1),
        @quarter date = DATEFROMPARTS(YEAR(@day), MONTH(@day)-((MONTH(@day)-1)%3),1),
        @week    date = DATEADD(DAY,1-DATEPART(WEEKDAY,@day),@day);
SELECT * FROM sys.sysdatabases
  -- for the given quarter:
  WHERE crdate >= @quarter AND crdate < DATEADD(QUARTER, 1, @quarter)

Summary

Date period calculations and date range queries can be written in much more elegant ways. I wrote them the hard way for a long time, because that's how we had to do it, but have embraced new functions like DATEFROMPARTS() to make my code much more intuitive and self-documenting. In addition, by finding the beginning of the next period instead of the end of the current one, I'm able to ensure my reports are accurate – no matter what happens to the data types of the underlying columns or input parameters.

Next Steps

Read on for related tips and other resources:

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

View all my tips

Comments For This Article

Wednesday, January 4, 2023 - 9:49:54 AM - Aaron

Hi @Kesava, check out this series:

https://www.mssqltips.com/sqlservertip/3173/handle-conversion-between-time-zones-in-sql-server-part-1/?utm_source=AaronBertrand

https://www.mssqltips.com/sqlservertip/3174/handle-conversion-between-time-zones-in-sql-server-part-2/?utm_source=AaronBertrand

https://www.mssqltips.com/sqlservertip/3175/handle-conversion-between-time-zones-in-sql-server-part-3/?utm_source=AaronBertrand

Wednesday, January 4, 2023 - 9:08:54 AM - Kesava	Back To Top (90796)
How to convert UTC dateand time to EST automatically with out any manual work. Suppose if it is DST then UTC-5. If no DST then it should be UTC-4.

Wednesday, October 12, 2022 - 8:16:00 AM - Xavier Bracco	Back To Top (90590)
Excellent article. Since I have to travel between the DB2s down to the other zillion SQLs calculating dates is a royal pain. Love this one indeed.

Monday, December 30, 2019 - 10:56:50 AM - Aaron Bertrand

@William, I do see a lot of people use date as int, but I'm not sure how it addresses the problem outlined in this tip. You lose plenty of things:

- built-in validation (in your model, what prevents 20200231 or 20201345 or 20200000?)

- more importantly, built-in datepart / dateadd functionality (how do you get the 3rd day of the month, modulo? how does using an int help you get the 3rd Monday or the 74th day of the year or the first day of a month or quarter? If you have to convert the entire column to a date type first, what was the benefit of storing it as an int?)

I understand people have reasons for using an int instead of a date. I don't necessarily agree with them, so I'm not going to debate them here, I'm just trying to understand how it's relevant to the problem at hand.

Saturday, December 28, 2019 - 10:42:39 PM - William Holz	Back To Top (83547)
I just use a date dimensions with an int key in YYYYMMDD format.

Friday, July 5, 2019 - 3:59:07 PM - Toby Ovod-Everett

You can simplify the quarter calculation like so:

(MONTH(@day)-1)/3*3+1

This takes advantage of integer arithmetic in SQL.

Friday, June 28, 2019 - 10:08:59 AM - Aaron Bertrand	Back To Top (81625)
Iain, it's not that useful. Most of us have data stored in datetime, which means the end of the month isn't really the last day of the month at midnight. If you find it useful, great. It's not useful for most (and using it in edge cases where it doesn't make any sense in other cases is definitely not something I can condone or recommend).

Friday, June 28, 2019 - 7:49:04 AM - Iain Barnetson

EOMONTH is a usefull function for working with dates.

SELECT EOMONTH ( GETDATE() ) AS 'This Month';

This Month

2019-06-30

SELECT EOMONTH ( GETDATE(), 1 ) AS 'Next Month';

Next Month

2019-07-31

SELECT EOMONTH ( GETDATE(), -1 ) AS 'Last Month';

Last Month

2019-05-31

Thursday, June 27, 2019 - 11:31:58 AM - John Spencer

Yes, the article is quite informative. And, can corroborate your statements regarding there are quite complicate statements to get various dates/ranges. I, too, plead guilty.

Many Thanks!!

Will look to see if I can some ‘simplicity’ in handling dates — with your help.

John Spencer