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

DECLARE @date date, @datetime datetime<br>  SELECT @datetime = GETDATE(), @date = @datetime<br>  SELECT @date AS 'date', @datetime AS 'datetime'<br><br>----------------------------------------------------------------- <br>2010-01-11 2010-01-11 15:40:14.510


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.

DECLARE @time time, @datetime datetime<br>  SELECT @datetime = GETDATE(), @time = @datetime<br>  SELECT @time AS 'time', @datetime AS 'datetime'<br>----------------------------------------------------------------- <br>15:42:36.7100000 2010-01-11 15:42:36.710


3. Convert from datetime to datetime2
This conversion is straightforward: Both the date and time of the datetime data type are copied.

DECLARE @datetime2 datetime2, @datetime datetime<br>  SELECT @datetime = GETDATE(), @datetime2 =<br>   @datetime  SELECT @datetime2 AS 'datetime2', @datetime<br>   AS 'datetime'<br>----------------------------------------------------------------- <br>2010-01-11 15:44:57.85 2010-01-11 15:44:57.853


4. Convert from time to datetime
Hours, 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.

DECLARE @time time(4), @datetime datetime<br>  SELECT @time = '10:11:12.1234', @datetime = <br>  @time  SELECT @time AS 'time', @datetime   AS 'datetime'<br>----------------------------------------------------------------- <br>10:11:12.1234 1900-01-01 10:11:12.123


5. Convert from date to datetime
The date values in the date data type are converted, but the time portion of the datetime data type is set to zero.

DECLARE @date date, @datetime datetime<br>  SELECT @date = '01-11-10', @datetime = @date<br>  SELECT @date AS 'date', @datetime AS 'datetime'<br>----------------------------------------------------------------- <br>2010-01-11 2010-01-11 00:00:00.000


6. Convert from datetime2 to datetime
Here, 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.

DECLARE @datetime2 datetime2, @datetime datetime<br>  SELECT @datetime2 = GETDATE(), @datetime =<br>  @datetime2  SELECT @datetime AS 'datetime', <br>  @datetime2   AS 'datetime2'<br>----------------------------------------------------------------- <br>2010-01-11 15:50:56.617 2010-01-11 15:50:56.61