- Initializing and Incrementing Variables
- Inserting Multiple Rows with a Single Statement
- Merging Multiple Data Sets
- Separate Date and Time Data Types
- New Date- and Time-Related Functions
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
New Date- and Time-Related Functions
SQL Server 2008 introduced several new functions to support new date and time data types.
- [lb] You can use the SYSTEMDATETIME() function to retrieve current date and time and take full advantage of sub-second precision with the DATETIME2 data type. The resulting value uses the DATETIME2(7) data type, showing seven digits to the right of the decimal point for fractions of a second.
- [lb] The SYSUTCDATETIME() function uses the same data type and precision as SYSTEMDATETIME() to report the current date and time in UTC.
- [lb] The SYSDATETIMEOFFSET() function reports the current date and local time, as well as the difference between the local and UTC time zones. For example, the following output indicates that my time zone is five hours behind UTC:
SELECT SYSDATETIMEOFFSET()
Result:
2011-07-01 16:49:18.4531250 -05:00
The SWITCHOFFSET function provides a way to adjust the time zone on a DATETIMEOFFSET value. For example, suppose we had date and time values reported in Eastern Standard Time, which we want to convert to UTC. We could use code similar to the following:
DECLARE @dateEST DATETIMEOFFSET = '07-01-2011 05:00:00 -05:00' DECLARE @dateUTC DATETIMEOFFSET SELECT @dateUTC = SWITCHOFFSET(@dateEST,'-00:00') SELECT @dateUTC AS date_in_UTC
Result:
|
date_in_UTC |
|
2011-07-01 10:00:00.0000000 +00:00 |
As you can see from the result, 5 AM Eastern Standard Time translates into 10 AM UTC on the same date. Had we translated 10 PM Eastern Standard Time into UTC using the SWITCHOFFSET function, the new value would show 3 AM of the next day.
The TODATETIMEOFFSET function is similar to SWITCHOFFSET in the sense that it changes the time zone. The difference between the two functions is that SWITCHOFFSET translates the time of the original value to the time of the newly specified time zone. TODATETIMEOFFSET function doesn't adjust the time value; it just changes the time zone. For example:
DECLARE @date1 DATETIME = '07-01-2011 00:01:00' DECLARE @dateUTC DATETIMEOFFSET SELECT @dateUTC = TODATETIMEOFFSET(@date1,'-00:00') SELECT @dateUTC AS date_in_UTC
Result:
|
date_in_UTC |
|
2011-07-01 00:01:00.0000000 +00:00 |
Notice that the returned value is aware of the time zone, but it still shows 1 AM[md]the time value is not adjusted to reflect the UTC time zone.
The first parameter of the TODATETIMEOFFSET function could be of any date and time data type, including DATETIME, SMALLDATETIME, DATETIME2, DATE, TIME, or DATETIMEOFFSET. If you don't pass the time value, the result will default to midnight; if you don't pass the date (using the TIME data type), the result will default to January 1, 1900.
What's Next?
In part 2 of this series, we'll continue our brief look at new and changed Transact-SQL functionality in SQL Server 2008 by examining functions for grouping, as well as using table-valued parameters to pass multiple data rows to a stored procedure.