Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Simplify Date Period Calculations in SQL Server


By:   |   Updated: 2019-06-27   |   Comments (4)   |   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:



Last Updated: 2019-06-27


get scripts

next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a Product Manager at SentryOne, 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 serves as a community moderator for the Database Administrators Stack Exchange.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, July 05, 2019 - 3:59:07 PM - Toby Ovod-Everett Back To Top

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

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 Back To Top

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 Back To Top

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 


Learn more about SQL Server tools