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:
