SQL Server 2008 Date Functions, Part 2
June 26, 2008
In my last months article, I talked about the new date and time data types that are being introduced with SQL Server 2008. These new date and time data types support an extended range of dates, and time that includes greater precision. To manage and use these new date data types Microsoft has provided a number of new system functions. In this article, I will be exploring these new system functions and some of the old date functions that were available in older releases of SQL Server.
SQL Server 2008 allows you to store more precision (up to 7 decimal places) for the time portion of a date in some of the new data types. The familiar GETDATE function, which you have been using to get the latest date and time from the system, doesnt support returning more than 3 decimal places for the fractional portion of seconds. In order to get the additional precision made available with these new date/time data types you need to use the new SYSDATETIME function. Here is an example of how to use this function:
CREATE TABLE Product (ID int identity, Product varchar(30), ManufactureDate datetime2); INSERT INTO Product values ('Widget',SYSDATETIME()), ('WingDing',SYSDATETIME()); SELECT Product, cast(ManufactureDate as time) as [Manufacture TIME] FROM Product; DROP TABLE Product;
When I run this code, I get the following output:
Product Manufacture TIME ------------------------------ ---------------- Widget 12:30:29.9696464 WingDing 12:30:29.9696464
Here I displayed the Product and the Manufacture Time which is just the time portion of the DATETIME2 column named ManufactureDate. This column was populated using the SYSDATETIME function in the INSERT statement, just like you would using the old GETDATE() function. Using this function allowed me to store 7 digits of precision in the ManufactureDate column.
The SYSDATETIMEOFFSET function is used to return a DATETIMEOFFSET(7) formatted date/time value. This function provides you with a method to populate a DATETIMEOFFSET data type column with the current system date and time with a time zone offset. Here is an example of how to use this function:
CREATE TABLE CallTicket (TicketID int identity, TicketDesc varchar(max), TicketDate datetimeoffset); INSERT INTO CallTicket values ('Individual is having problem with...',SYSDATETIMEOFFSET()), ('Sofware crashes with a code 112233 ...',SYSDATETIMEOFFSET()); SELECT TicketID, TicketDate FROM CallTicket; DROP TABLE CallTicket;
When I run this, I get the following output:
TicketID TicketDate ----------- ---------------------------------- 1 5/27/2008 5:41:23 PM -07:00 2 5/27/2008 5:41:23 PM -07:00
Here you can see my machine sets the TicketDate to my machines local time, which is this case has time zone value of -07:00.
The TODATETIMEOFFSET function changes the time zone of a DATETIMEOFFSET data type. To use this function you pass two parameters. One parameter is a DATETIMEOFFSET data type value and the other is a time zone value. You might want to do this if you copy a database that stores the local time in a DATETIMEOFFSET column to another system that has another time zone and you want to make the date reflect the time zone of the new system. Here is an example that uses this function:
SET NOCOUNT ON; DECLARE @TD DATETIMEOFFSET; SET @TD = SYSDATETIMEOFFSET(); SELECT @TD [Old Time Zone]; SELECT TODATETIMEOFFSET(@TD,'+06:00') [New Time Zone];
When I run this code on my machine, I get the following output:
Old Time Zone ---------------------------------- 6/12/2008 5:51:49 AM -07:00 New Time Zone ---------------------------------- 6/12/2008 5:51:49 AM +06:00
In this code, I set the DATETIMEOFFSET variable to the current system time, and then use the TODATETIMEOFFSET function to change the time zone to +06:00. As you can see, the first SELECT statement has set the time zone of the @TD variable to -07:00, which is my local time zone. Then when I use the TODATETIMEOFFSET function the time zone changed to +06:00, it changes only the time zone value and not the actual time.
The SWITCHOFFSET function is similar to the TODATETIMEOFFSET function, but instead of only changing the time zone value of a DATETIMEOFFSET data type, this function also changes the time as well. When you use this function the new date, time and time zone will be adjusted to reflect what time it would be in the new time zone based on the date/time being changed. This function is useful when displaying a date that matches a specific time zone even when the base date contains a date that represents a different time zone. This function will be great when trying to support an application that supports users in multiple time zones. Here is an example that demonstrates how to use this new function:
CREATE TABLE TimeZone (ID int identity, LocalTime datetimeoffset); INSERT INTO TimeZone values ('2008-05-21 17:50:01.1234567 +08:00'), ('2008-05-21 18:50:01.1234567 +08:00'); SELECT * FROM TimeZone; SELECT ID, SWITCHOFFSET(LocalTime,'+06:00') [time for new time zone] FROM TimeZone; DROP TABLE TimeZone; ID LocalTime ----------- ---------------------------------- 1 5/21/2008 5:50:01 PM +08:00 2 5/21/2008 6:50:01 PM +08:00 ID time for new time zone ----------- ---------------------------------- 1 5/21/2008 3:50:01 PM +06:00 2 5/21/2008 4:50:01 PM +06:00
Here you can see the first SELECT statement shows the original values of the LocalTime column that have a time zone value of +08:00. Next, I display a different time by switching the time zone of the LocalTime to +06:00 using the SWITCHOFFSET function. As you can see this function not only changed the time zone, but it also changed the time value to represent the new time zone. If you look at ID=1 the original time was 5:50.01 PM, after using the SWITCHOFFSET function, to change the time zone by 2 zones (from +08:00 to + 06:00), the new LocalTime value is now 3:50:01 PM.
Things to Think About When Using Other Date/Time Functions
With the new date/time date types that are available within SQL Server 2008, SQL Server now supports dates from 0001-01-01 to 9999-01-01. You need to keep this in mind when using the old date/time functions that were available in the old versions of SQL Server, and also available in SQL Server 2008. These functions may or may not work with the new date and time data types. Below I will look at each of these old functions that were available in SQL Server 2005, and discuss issues around using them along with the new date and time data types.
The ISDATE function is used to validate dates. This function only works to validate SMALLDATETIME or DATETIME date types. This function will return an error if you pass it on any of the new date related data type. The ISDATE function can also be passed a string, but will only identify that a string contains a valid date if the string contains a valid DATETIME date value, meaning the string must fall within the following range: January 1, 1753 through December 31, 9999. So if you run the following code in SQL Server 2008:
SELECT CASE WHEN ISDATE('1752-12-31') = 1 THEN 'Valid Date' ELSE 'Invalid Date' END; SELECT CASE WHEN ISDATE('1753-01-01') = 1 THEN 'Valid Date' ELSE 'Invalid Date' END;
You get the following output:
------------ Invalid Date ------------ Valid Date
As you can see, the date 1752-12-31 is not a valid date. You need to be aware of this when you start trying to validate strings that contain dates that will have a destination data type of DATE, DATETIME2 or DATETIME2OFFSET. There doesnt seem to be a new function to validate those extended dates for SQL Server 2008. If you normally validate dates using the ISDATE function prior to storing them in a date/time data type you need to be aware that this function will not validate all dates which are valid for DATE, DATETIME2, or DATETIME2OFFSET data type.
The DATEADD function also does not fully support the new date/time data types. Although this function will support the new date data types if you call the function a specific way. Let me explain.
In the CTP 6 version of SQL Server 2008 if you pass a string that contains a valid date string the DATEADD function fails. So, if you want use this function to perform the date arithmetic operations for the new valid date ranges you need to provide the function with a variable or column, and not a string. Let me demonstrate.
In SQL Server 2005 and before (and SQL Server 2008 for that matter) you could run the following code to add 1 day to a specific date without getting an error.
DECLARE @d datetime SET @d = '1900-01-01' SELECT DATEADD(DAY,1,@d), DATEADD(DAY,1,'1900-01-01')
You can run the code above in SQL Server 2008 and it will run just fine. But if you change the code slightly to look like this and run it in SQL Server 2008:
DECLARE @d datetime2 SET @d = '0001-01-01' SELECT DATEADD(DAY,1,@d), DATEADD(DAY,1,'0001-01-01')
You get the following conversion error:
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
You get this error because the DATEADD function cant convert 0001-01-01 to a valid datetime data type, because it is not a valid date that for that date type. But this code is valid:
DECLARE @d datetime2 SET @d = '0001-01-01' SELECT DATEADD(DAY,1,@d)
This means you need to be careful when dealing with the DATEADD function. This functionality holds true for both the DATE and DATETIMEOFFSET data types. Make sure you dont use literal strings that contain dates that are only valid for the new date data types.
The DATEDIFF function allows you to compare two dates and return the difference. This function seems to accept the new data type that contains a date that wasnt supported prior to SQL Server 2008. Here is an example of where I can pass a DATETIME2 value and have it correctly product the difference between two dates.
DECLARE @d1 DATETIMEOFFSET; SET @d1 = '1700-04-01'; DECLARE @d2 DATETIMEOFFSET; SET @d2 = '1701-04-01'; SELECT DATEDIFF(DAY,@d1,@d2) Variable_Diff, DATEDIFF(DAY,'1700-04-01','1701-04-01') String_Diff;
When I run this code, I get this output:
Variable_Diff String_Diff ------------- ----------- 365 365
Here you can see passing a variable or a literal string works.
Beware of Date Function
Using the date functions available in SQL Server 2008 a person needs to understand how they are affected when using them with any of the new date/time data types. You need to beware of those functions that were available in SQL Server 2005 that either dont work with new data types, or only work a specify way. By not understanding how the new date/time data types affect these older functions you might find your application errors out, or gets unexpected results. Now that SQL Server supports larger precision and time zone information you need to make sure you use the correct new SQL Server functions in order to populate your date correctly.