Microsoft SQL Server 2008 R2 Unleashed- P164:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1574 CHAPTER 42 What s New for Transact-SQL in SQL Server 2008 @time3 as times select @datetime2 as datetime2 @datetimeoffset as datetimeoffset @utcdatetime as utcdatetime select SYSDATETIMEOFFSET as sysdatetimeoffset SYSDATETIME as sysdatetime go datetime date time time3 2010-03-28 23 18 2010-03-28 23 18 23 18 datetime2 datetimeoffset utcdatetime 2010-03-28 23 18 2010-03-28 23 18 -04 00 2010-03-29 03 18 sysdatetimeoffset sysdatetime 2010-03-28 23 24 -04 00 2010-03-28 23 24 Be aware that retrieving the value from getdate or sysdatetime into a datetimeoffset variable or column does not capture the offset from UTC even if you store the returned value in a column or variable defined with the datetimeoffset data type. To do so you need to use the SYSDATETIMEOFFSET function declare @datetimeoffset1 datetimeoffset @datetimeoffset2 datetimeoffset select @datetimeoffset1 SYSDATETIME @datetimeoffset2 SYSDATETIMEOFFSET select @datetimeoffset1 @datetimeoffset2 go 2010-03-28 23 36 00 00 2010-03-28 23 36 -04 00 Note that in the output SQL Server Management Studio SSMS trims the time values down to two decimal places when it displays the results in the Text Results tab. However this is just for display purposes and applies only with text results grid results display the full decimal precision . The actual value does store the precision down to the specified number of decimal places which can be seen if you convert the datetime2 value to a string format that displays all the decimal places New date and time Data Types and Functions 1575 select SYSDATETIME as datetime2_trim convert varchar 30 SYSDATETIME 121 as datetime2_full go datetime2 trim datetime2 full 2010-03-30 23 52 2010-03-30 23 52 The SWITCHOFFSET function can be used to convert a datetimeoffset value into a different time zone offset value select SYSDATETIMEOFFSET SWITCHOFFSET SYSDATETIMEOFFSET -07 00 go 42 .
