In SQL 2008, Microsoft introduced some new date and time data types to augment the options available in prior versions. The full list of these data types with detailed explanations can be found in Microsoft Books on Line (BOL), but we’ll list them here with a very brief description.
- DATETIME – This is the standard and probably most commonly used type that’s been available in T-SQL since its early days, with a range of 1753-01-01 through 9999-12-31 and accuracy of about 3 milliseconds. Note that if you cast (implicitly or explicitly) an INT value of 0 to this data type, the result will be 1900-01-01.
- DATE – This is a new type that is accurate to the day only (no time component) and has a range of 0001-01-01 through 9999-12-31.
- DATETIME2 – This is a higher-accuracy DATE + TIME type that is accurate to 100 nanoseconds (or .0000001 seconds) and has a range of 0001-01-01 through 9999-12-31.
- DATETIMEOFFSET – This is a DATE + TIME type that includes the UTC time zone offset with varying degrees of accuracy (you can specify) and has a range of 0001-01-01 through 9999-12-31.
- SMALLDATETIME – This is another DATE + TIME type that has an accuracy of one minute (no seconds) and a date range of 1900-01-01 through 2079-06-06.
- TIME – This is a TIME-only type that is accurate to 100 nanoseconds and has a range of 00:00:00.0000000 through 23:59:59.9999999.
This blog is less about the date and time data types and really about the different ways they can be manipulated. We’re not talking about formatting here (for that you’d use CONVERT), instead we’re talking about how to do date arithmetic and the functions that SQL provides you to do so. Normally date formatting should be done in an application’s front end, but it is often quite useful and necessary to do date calculations in the T-SQL back end.
Simple Date Arithmetic
If you have a DATETIME data column, or perhaps you’re using T-SQL’s GETDATE() built-in function, if you want to add a fixed number of days, that is very simple:
SELECT GETDATE(), GETDATE()-1, GETDATE()+1; -- Results: 2014-03-05 11:29:37.087 2014-03-04 11:29:37.087 2014-03-06 11:29:37.087
Often this approach can be faster than using the T-SQL built in function for adding dates (to be discussed in a minute).
Unfortunately, this doesn’t work well with any of the other date and time data types except for SMALLDATETIME:
SELECT CAST(GETDATE() AS DATE)+1;
GO
SELECT CAST(GETDATE() AS DATETIME2)+1;
GO
SELECT CAST(GETDATE() AS DATETIMEOFFSET)+1;
GO
SELECT CAST(GETDATE() AS TIME)+1;
GO
-- Errors returned:
Operand type clash: date is incompatible with int
Operand type clash: datetime2 is incompatible with int
Operand type clash: datetimeoffset is incompatible with int
Operand type clash: time is incompatible with int
The same link provided above for CONVERT describes CAST. There are those that like to ignore the myth of SQL code compatibility and recommend that instead of using GETDATE() you use CURRENT_TIMESTAMP (the ANSI standard function that returns DATETIME), but I am not one of them.
If you need to do any other date arithmetic, SQL provides a built in function called DATEADD. It can be used to add a fixed number of days, hours, seconds, months, etc. to any date/time data type (although you will get an error adding days, weeks, months, etc. to a TIME data type). The first argument to DATEADD tells the function what unit you want to add, while the second specifies the number of units. The last argument is the date/time value you want to add those units to. So we can fix our code above to add one day to each of the supported date/time types.
SELECT GETDATE(); SELECT DATEADD(day, 1, CAST(GETDATE() AS DATE)); SELECT DATEADD(day, 1, CAST(GETDATE() AS DATETIME2)); SELECT DATEADD(day, 1, CAST(GETDATE() AS DATETIMEOFFSET)); SELECT CAST(GETDATE() AS SMALLDATETIME)+1; SELECT DATEADD(hour, 1, CAST(GETDATE() AS TIME)); -- Results: 2014-03-05 11:43:53.117 2014-03-06 2014-03-06 11:43:53.1170000 2014-03-06 11:43:53.1170000 +00:00 2014-03-06 11:46:00 12:43:53.1170000
Those results also clearly demonstrate the accuracy of each of the data types.
Another extremely useful function for doing date arithmetic is DATEDIFF, which is used to calculate the difference between two dates (or times) in whole units as specified by its first argument. Let’s take a look at an example.
SELECT DATEDIFF(day, '2014-04-15', '2014-04-17'); -- Results: 2
The result is negative if the left date is greater than the right date. The first argument to DATEDIFF is the same set of units you can specify to DATEADD.
Date Truncation
In an earlier blog on Tally Tables and another one on Calendar Tables, we’ve seen that DATEADD and DATEDIFF can be combined to perform date truncation on a date part boundary. Now we’ll explain exactly how that works. Let’s take a look at the T-SQL for the most common case (truncation to the day):
SELECT GETDATE(); -- Take the days difference between today's date and 1900-01-01 SELECT DATEDIFF(day, 0, GETDATE()); -- Add back the days difference to 1900-01-01 SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0); -- Results: 2014-03-05 12:02:51.870 41701 2014-03-05 00:00:00.000
If today’s date is 2014-03-05, the number of days since 1900-01-01 (=0 remember that this is the base date when 0 is cast to DATETIME) is 41701. We can add back that number of days to 1900-01-01 and get exactly today’s date without the time part.
Likewise, we can truncate to the minute, hour, month or year simply by specifying a different first argument to both functions:
SELECT GETDATE(); SELECT DATEADD(minute, DATEDIFF(minute, 0, GETDATE()), 0); SELECT DATEADD(hour, DATEDIFF(hour, 0, GETDATE()), 0); SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0); SELECT DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0); -- Results: 2014-03-05 12:08:51.573 2014-03-05 12:08:00.00 2014-03-05 12:00:00.000 2014-03-01 00:00:00.000 2014-01-01 00:00:00.000
You will run into an error however if you try it to the second:
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
But you can work around this by specifying a later offset date (2010-01-01 should work for a few more years):
SELECT DATEADD(second, DATEDIFF(second, '2010-01-01', GETDATE()), '2010-01-01'); -- Results: 2014-03-05 12:08:51.000
So let’s try a quick exercise to demonstrate our new found skill with date arithmetic. Try to solve it before you look at the solution. How would you truncate a DATETIME to yesterday at 18:00:00.000?
SELECT GETDATE(); SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE())-1, '18:00'); -- Results: 2014-03-05 12:17:36.210 2014-03-04 18:00:00.000
In this case, the time value (18:00) we specified at the end is up-cast to DATETIME 1900-01-01 18:00:00.000 and that is what the days offset (from 1900-01-01) is added back to.
More of these really neat and useful but simple date arithmetic examples can be found in this blog by Lynn Pettis, who I like to think of as the guru of date manipulations: Some Common Date Routines.
Combining Date Components
In a database, dates should always be stored as dates (DATETIME) and not character strings or their individual date parts. Unfortunately, not everybody realizes this and sometimes make the mistake of storing dates as the individual parts. Let’s return now to an example of some T-SQL from our Calendar Tables blog. We’ll assume you still have access to the auxiliary Calendar TABLE we created in that blog.
SELECT [Date], [Year], [Month], [Day] FROM dbo.Calendar WHERE [Date] >= '2013-03-01' AND [Date] < '2013-03-05'; -- Results: Date Year Month Day 2013-03-01 00:00:00.000 2013 3 1 2013-03-02 00:00:00.000 2013 3 2 2013-03-03 00:00:00.000 2013 3 3 2013-03-04 00:00:00.000 2013 3 4
Using our newly found knowledge of date arithmetic and the T-SQL built in functions to handle them, we can easily re-assemble the Year, Month and Day columns in this result to be a DATE or DATETIME.
SELECT [Date], [Year], [Month], [Day]
,[AsDATETIME]= [Day]+DATEADD(month,[Month]-1,DATEADD(year,[Year]-1900,0)-1
,[AsDATE] = CAST([Day]+DATEADD(month,[Month]-1,DATEADD(year,[Year]-1900,0))-1 AS DATE)
FROM dbo.Calendar
WHERE [Date] >= '2013-03-01' AND [Date] < '2013-03-05';
-- Results:
Date Year Month Day AsDATETIME AsDATE
2013-03-01 00:00:00.000 2013 3 1 2013-03-01 00:00:00.000 2013-03-01
2013-03-02 00:00:00.000 2013 3 2 2013-03-02 00:00:00.000 2013-03-02
2013-03-03 00:00:00.000 2013 3 3 2013-03-03 00:00:00.000 2013-03-03
2013-03-04 00:00:00.000 2013 3 4 2013-03-04 00:00:00.000 2013-03-04
As you can see, we’ve subtracted the base year (1900) from Year, adding that number of years back to the base year (0=1900-01-01), then added Month-1 months to that and finally one less than Day to that. Our results are just what we need and we achieved them by using just a little simple date arithmetic. This will usually be faster than converting to a character string and then manipulating that, ultimately CASTing it to the desired type.
Casting DATETIMEs to Character Strings
If you’ve never tried to CAST a DATETIME to a character string, you might be a little surprised by the result you get.
SELECT GETDATE(); SELECT CAST(GETDATE() AS VARCHAR(100)); -- Results: 2014-03-05 14:13:42.760 Mar 5 2014 2:13PM
Why Microsoft chose this particular result is probably a mystery to all except them. But knowing this behavior does offer the occasional opportunity for CASTing in the other direction. Consider these cases:
SELECT CAST('Mar 01 2013' AS DATETIME);
SELECT CAST('Mar 01 2013 15:00:03' AS DATETIME);
SELECT CAST('Mar 01 2013 2:05PM' AS DATETIME);
-- Results:
2013-03-01 00:00:00.000
2013-03-01 15:00:03.000
2013-03-01 14:05:00.000
Another somewhat interesting case is when you try casting a character string that T-SQL recognizes as a valid year.
SELECT CAST('2013' AS DATETIME);
SELECT CAST('201303' AS DATETIME);
SELECT CAST('2013-01' AS DATETIME);
-- Results:
2013-01-01 00:00:00.000
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Conversion failed when converting date and/or time from character string.
While it works with a year, when the character string looks like a combination of year and month, it results in one of the two error messages shown.
On the other hand, these two cases produce exactly what you’d expect, which is the DATETIME value 2013-01-02 00:00:00.000, regardless of whether the CAST is explicit or implicit.
SELECT CAST('2013-01-02' AS DATETIME);
SELECT CAST('20130102' AS DATETIME);
To CAST these to any of the other date/time data types, you must use an explicit CAST. An implicit CAST will always result in a DATETIME.
Let’s also not forget the other CAST we learned from our exercise:
SELECT CAST('15:00' AS DATETIME)
-- Results:
1900-01-01 15:00:00.000
Summary
In this blog we’ve learned about the T-SQL data types that support dates and times, including the range and accuracy of each.
We have learned how to perform some simple date arithmetic and apply that knowledge to truncating a DATETIME and reassembling the date parts into a DATETIME.
Finally, we’ve learned a little about the results of casting a DATETIME to a character string and vice versa.
All of these date manipulation constructs can be considered T-SQL best practices if you need to use them. In general date arithmetic will be faster than extracting the parts as character strings, manipulating those character strings and then reassembling them and CASTing back to the date/time data type you need.
There are many other date functions offered by T-SQL and you can read about them in BOL. If you go back to the Calendar Tables blog (linked in above), you can now go back and try to understand how the GenerateCalendar function we provided there actually works.
I hope you enjoyed this introduction to manipulating dates and times in T-SQL.
Follow me on Twitter: @DwainCSQL
Copyright © Dwain Camps 2014 All Rights Reserved
