Skip navigation

Converting Data Types

Take advantage of the new 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

DECLARE @date date, @datetime datetime
SELECT @datetime = GETDATE(), @date = @datetime
SELECT @date AS 'date', @datetime AS 'datetime'

-----------------------------------------------------------------
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
SELECT @datetime = GETDATE(), @time = @datetime
SELECT @time AS 'time', @datetime AS 'datetime'
-----------------------------------------------------------------
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
SELECT @datetime = GETDATE(), @datetime2 =
@datetime SELECT @datetime2 AS 'datetime2', @datetime
AS 'datetime'
-----------------------------------------------------------------
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
SELECT @time = '10:11:12.1234', @datetime =
@time SELECT @time AS 'time', @datetime AS 'datetime'
-----------------------------------------------------------------
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
SELECT @date = '01-11-10', @datetime = @date
SELECT @date AS 'date', @datetime AS 'datetime'
-----------------------------------------------------------------
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
SELECT @datetime2 = GETDATE(), @datetime =
@datetime2 SELECT @datetime AS 'datetime',
@datetime2 AS 'datetime2'
-----------------------------------------------------------------
2010-01-11 15:50:56.617 2010-01-11 15:50:56.61
Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish