Date/Time Calculations: Part 2
In Part 1 of this topic, we considered how input dates can be manipulated to dynamically generate whatever ranges a query needs. As promised, it’s time to look at more intriguing combinations.
So, to start: A common requirement in many applications is to do calculations based on time periods such as “every day between last Friday and today.” By modifying the truncation pattern a bit, finding “last Friday” is fairly simple. The main trick is to start with a well-known reference date. In this case, to find a day on Friday, the reference date should be any Friday. We know that the number of days between any Friday and any other Friday is divisible by seven, and we can use that knowledge to truncate the current date down to the nearest Friday.
The following T-SQL finds the number of days between the reference Friday, January 7, 2005, and the input date, February 3, 2011:
DECLARE @Friday DATETIME SET @Friday = '20050107' SELECT DATEDIFF(day, @Friday, '20110203')
The result is 2218, which of course is an integer. Taking advantage of SQL Server’s integer math properties, dividing the result by 7 and then multiplying it by 7 again will round it down to the nearest number divisible by seven, 2212:
SELECT (2218 / 7) * 7
Adding 2212 days to the original reference date of January 7, 2005, results in the desired output, the “last Friday” before February 3, 2011, which was on January 28, 2011:
SELECT DATEADD(day, 2212, '20050107')
As with the previous example, this can be simplified (and clarified) by combining everything inline:
DECLARE @Friday DATETIME SET @Friday = '20050107' SELECT DATEADD(day, ((DATEDIFF(day, @Friday, @InputDate) / 7) * 7), @Friday)
A further simplification of the second statement is also possible. Once the result of the inner
DATEDIFF is divided by 7, its granularity is in weeks until it is multiplied by 7 again to produce days. But there is no reason to do the second multiplication, and the code becomes a bit cleaner if the result is simply treated as weeks for the final calculation:
SELECT DATEADD(week, (DATEDIFF(day, @Friday, @InputDate) / 7), @Friday)
Note that these examples return the input date itself (with the time portion truncated), if the input date is a Friday. If you really want to return the “last” Friday every time, and never the input date itself—even if it is a Friday—a small modification is required. To accomplish this, you must use two reference dates: one for any Friday, and one for any day within a week of the original reference date. (I recommend the next day, for simplicity.) By using the next day as the inner reference date, the result of the division will be one week lower if the input date is a Friday, meaning that the result will be the previous Friday. The following T-SQL does this for a given input date:
DECLARE @Friday DATETIME SET @Friday = '20050107' DECLARE @Saturday DATETIME SET @Saturday = '2005010 SELECT DATEADD(week, (DATEDIFF(day, @Saturday, @InputDate) / 7), @Friday)
By using this pattern and switching the reference date, you can easily find the last of any day of the week given an input date. To find the “next” one of a given day (e.g. “next Friday”) simply add one week to the result of the inner calculation before adding it to the reference date:
SELECT DATEADD(week, (DATEDIFF(day, @Friday, GETDATE()) / 7)+1, @Friday)
As a final example of what you can do with date/time calculations, a slightly more complex requirement is necessary. Say that you’re visiting the Boston area, and want to attend a meeting of the New England SQL Server User Group. The group meets on the second Thursday of each month. Given an input date, how do you find the date of the next meeting?
To find the second Thursday of the month of the input date, first find the fourteenth day of the month, then use it to find “last Thursday.” The fourteenth day has significance because if the first of the month is a Thursday, the second Thursday will be the eighth day of the month. On the other hand, if the first of the month is a Friday, the second Thursday will be the fourteenth. So for any given month, the “last Thursday” (as of and including the fourteenth) will be the second Thursday of the month. The following T-SQL does exactly that
DECLARE @Thursday DATETIME SET @Thursday = '20050414' DECLARE @FourteenthOfMonth DATETIME SET @FourteenthOfMonth = DATEADD(month, DATEDIFF(month, @Thursday, @InputDate), @Thursday) SELECT DATEADD(week, (DATEDIFF(day, @Thursday, @FourteenthOfMonth) / 7), @Thursday)
Of course, this doesn’t find the next meeting; it finds the meeting for the month of the input date. To find the next meeting, a CASE expression will be necessary, in addition to an observation about second Thursdays: If the second Thursday of a month falls on the eighth, ninth, or tenth, the next month’s second Thursday is five weeks away. Otherwise, the next month’s second Thursday is four weeks away. To find the day of the month represented by a given date/time instance, use T-SQL’s
DATEPART function, which takes the same date granularity inputs as
DATEDIFF. The following T-SQL combines all of these techniques to find the next date for a New England SQL Server User Group meeting, given an input date:
DECLARE @Thursday DATETIME SET @Thursday = '20050414' DECLARE @FourteenthOfMonth DATETIME SET @FourteenthOfMonth = DATEADD(month, DATEDIFF(month, @Thursday, @InputDate), @Thursday) DECLARE @SecondThursday DATETIME SET @SecondThursday = DATEADD(week, (DATEDIFF(day, @Thursday, @FourteenthOfMonth) / 7), @Thursday) SELECT CASE WHEN @InputDate <= @SecondThursday THEN @SecondThursday ELSE DATEADD ( week, CASE WHEN DATEPART(day, @SecondThursday) <= 10 THEN 5 ELSE 4 END, @SecondThursday ) END
Finding complex dates like the second Thursday of a month is not a very common requirement unless you’re writing a scheduling application. More common are requirements along the lines of “find all of today’s rows.” Combining other range techniques with the date/time math seen here, it becomes easy to design stored procedures that both efficiently and dynamically query for required time periods. For instance, the following T-SQL always returns rows for the given day, no matter what the day is:
SELECT * FROM VariousDates WHERE ADate >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AND ADate < DATEADD(day, DATEDIFF(day, 0, GETDATE())+1, 0)
The first search argument uses the calculation to find “today” with the time part truncated, and the second search argument adds 1 to the difference in days between the current day and the reference date, to return “tomorrow” with the time part truncated. The result is all rows between today at midnight, inclusive, and tomorrow at midnight, exclusive.
As a final example of date/time calculations in T-SQL, consider a seemingly simple task: Find out how many years old you are as of today. The obvious answer is, of course:
SELECT DATEDIFF(year, @YourBirthday, GETDATE())
Unfortunately, this answer—depending on the current day—is wrong. Consider someone born on March 25, 1970. On March 25, 2011, that person’s forty-first birthday should be celebrated. Yet, according to SQL Server, that person was already 41 on March 24, 2011:
SELECT DATEDIFF(year, '19700325', '20110324')
As a matter of fact, according to SQL Server, this person was already 41 throughout the year of 2011, starting on January 1. Happy New Year! and Happy Birthday! combined, thanks to the magic of SQL Server? Probably not; the discrepancy is due to the way SQL Server calculates date differences. Only the date/time component being differenced is considered, and any components below are truncated. This feature makes the previous date/time truncation examples work, but makes age calculations fail because when differencing years, days and months are not taken into account.
To get around this problem, a
CASE expression must be added that subtracts one year if the day and month of the current date is less than the day and month of the input date. The following T-SQL, which both accomplishes the primary goal, and also takes leap years into consideration, was created by Steve Kass:
SELECT DATEDIFF ( YEAR, @YourBirthday, GETDATE() ) - CASE WHEN 100 * MONTH(GETDATE()) + DAY(GETDATE()) < 100 * MONTH(@YourBirthday) + DAY(@YourBirthday) THEN 1 ELSE 0 END
Note that this T-SQL uses the MONTH and DAY functions, which are shorthand for
DATEPART(month, <date>) and
DATEPART(day, <date>), respectively.