Date/Time Calculations: Part 1
Knowing how to format dates for input and output is a good first step, but the real goal of any database system is to allow the user to query the data to answer business questions. Expecting all date range queries to have hard-coded values for the input dates is neither a realistic expectation nor a very maintainable solution. By using SQL Server’s date calculation functions, input dates can be manipulated in order to dynamically come up with whatever ranges are necessary for a given query.
The two primary functions that are commonly used to perform date/time calculations are
DATEADD. The first returns the difference between two dates; the second adds (or subtracts) time from an existing date. Each of these functions takes granularity as a parameter, and can operate at any level between milliseconds and years.
DATEDIFF takes three parameters: the time granularity that should be used to compare the two input dates, the start date, and the end date. For example, to find out how many hours elapsed between midnight on February 13, 2011, and midnight on February 14, 2011, the following query could be used:
SELECT DATEDIFF(hour, '20110113', '20110114')
The result, as you might expect, is 24. Note that I mentioned that this query compares the two dates, both at midnight, even though neither of the input strings contains a time. Again, I want to stress that any time you use a string as an input where a date/time type is expected, it will be implicitly converted by SQL Server.
It’s also important to note that
DATEDIFF maintains the idea of “start” and “end” times, and the result will change if you reverse the two. Changing the previous query so that February 14 is passed before February 13 results in the output of -24.
DATEADD function takes three parameters: the time granularity, the amount of time to add, and the input date. For example, the following query adds 24 hours to midnight on February 13, 2011, resulting in an output of 2011-01-14 00:00:00.000:
SELECT DATEADD(hour, 24, '20110113')
DATEADD can also deal with negative amounts, and will subtract instead of add in that case.
The first step in doing date/time calculations in SQL Server is to learn to combine
DATEADD to truncate date/time values. Understanding how it’s done takes a bit of a logical jump, so I’ll break the process down into its component parts:
- Truncation is essentially the same as rounding down, so you must first decide which date/time component you’d like to use to do the rounding. The component you’ll use to do the rounding should be one level of granularity above whatever data you need to truncate. For instance, if you want to remove the seconds and milliseconds, you’d round down using minutes. Likewise, to remove the entire time portion, you’d round down using days.
- Once you’ve decided on a level of granularity, pick a reference date/time. For basic truncation, this date/time can be any within the range of the data type you’re working with. I generally use 0, which corresponds to 1900-01-01 at midnight for both
- Using the
DATEDIFFfunction, find the difference between the reference date/time and the date/time you want to truncate, at the level of granularity you’ve picked.
- Finally, use
DATEADDto add the output from the
DATEDIFFfunction to the same reference date/time that you used to find the difference. The result will be the truncated value of the original date/time.
Walking through an example should make this a bit clearer. Assume that you want to start with 2011-04-23 13:45:43.233 and truncate the time portion (in other words, come out with 2011-04-23 at midnight). The granularity used will be days, since that is the lowest level of granularity above the time granularities (milliseconds, seconds, minutes, and hours). The following T-SQL can be used to determine the number of days between the reference date of 0 and the input date:
DECLARE @InputDate DATETIME SET @InputDate = '2011-04-23 13:45:43.233' SELECT DATEDIFF(day, 0, @InputDate)
Running this T-SQL, we discover that 38828 days passed between the reference date and the input date. Using DATEADD, that number can be added to the reference date:
SELECT DATEADD(day, 38828, 0)
The result of this operation is the desired truncation: 2011-04-23 00:00:00.000. Because only the number of days was added back to the reference date—with no time portion—the date was rounded down and the time portion eliminated. Of course, you don’t have to run this T-SQL step-by-step; in a stored procedure, you’d probably combine everything into one inline statement:
SELECT DATEADD(day, DATEDIFF(day, 0, @InputDate), 0)
Once you understand this basic pattern, you can modify it to come up with any combination of dates. For instance, finding the first of the month is a simple matter of changing the granularity:
SELECT DATEADD(month, DATEDIFF(month, 0, @InputDate), 0)
Finding the last day of the month requires just a bit more thought; find the first day of the month, add an additional month, and subtract one day:
SELECT DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, @InputDate)+1, 0))
Another way to find the last day of the month is to use, as a reference date, any date which is the last day of a month. For instance, 1900-12-31:
SELECT DATEADD(month, DATEDIFF(month, '19001231', @InputDate), '19001231')
Note that the month you choose is important in this case. I chose a 31-day month; what this T-SQL is actually doing is finding the same day as the reference date, on the month in question. But if the month does not have 31 days, SQL Server will automatically round down to the closest date. Had I used February 28 instead of December 31 for the reference date, the output any time this query was run would be the 28th of the month.
Fun and rewarding, yes? In Part 2, I’ll suggest more interesting combinations, including pinning down something as ambiguous as the second Thursday of the month of the input date.