Converting Data Types

SQL Server 2008 introduced welcome and long-overdue data types, including the new date, time, and datetime2 data types. However, to take advantage of these new data types you’ll likely need to perform some conversion to and from your existing datetime data types. The code below shows how, with output examples below the dashed lines.

1. Convert from datetime to date In this conversion, the year, month, and day in the datetime data type are copied, but time values in the datetime data type are ignored.3

2. Convert from datetime to time Here, hours, minutes, seconds, and subseconds are transferred to the time data type. Fractional subsecond time values over three digits are truncated. Date values in the datetime data type are ignored.

4. Convert from time to datetimeHours, minutes, seconds, and subseconds are converted and fractional subsecond time values over three digits are truncated. The datetime data type’s date value is set to 1900-01-01.

6. Convert from datetime2 to datetimeHere, the date and time portion of the datetime2 data type are copied to the datetime data type, but fractional subsecond time values greater than three digits are truncated.