When it comes to converting date and time values in SQL Server, things can get a little complex. But in this article I hope to demystify this topic for anyone who’s confused when it comes to making conversions between dates and other data types, as well as changing the format of date/time values, and other considerations.

Let’s jump straight in.

In SQL Server, we convert between data types either explicitly or implicitly.

Explicit Conversion

When we explicitly convert data types, we use a function like CONVERT() or CAST() to do the conversion. When we do this, we explicitly tell SQL Server to convert from one data type to another, and we explicitly state which data type to convert to.

Example:

SELECT 
    GETDATE() AS "Original Value",
    CAST( GETDATE() AS date ) AS "Converted Value";

Result:

Original Value	         Converted Value
-----------------------  ---------------
2023-01-07 11:38:23.163	 2023-01-07

I just explicitly converted a datetime value to a date value. I used the CAST() function to convert the output of the GETDATE() function (which returns a datetime value containing the server’s date and time) to a date value.

As mentioned, another way to do it is to use the CONVERT() function.

The CAST() function is standards compliant while the CONVERT() function isn’t. But the CONVERT() function does allow us to specify a style for the date to be formatted in (more on date formats later).

We can alternatively use TRY_CAST() or TRY_CONVERT() to deal with any errors that may occur.

Implicit Conversion

When we implicitly convert data types, it’s actually SQL Server that does it behind the scenes. With an implicit conversion, SQL Server converts the data type indirectly as part of another operation.

For example, when we insert a value into a column, it will be implicitly converted into that column’s type (unless it’s already the correct type). The same when setting a variable. When we assign a value to a variable, an implicit data conversion may occur if we’re trying to assign a value of a different type to that variable. The value will be implicitly converted to the data type of the variable.

Here’s an example of an implicit conversion:

DECLARE @datetime datetime = '2035-12-30 22:34:43.125';
DECLARE @datetime2 datetime2 = @datetime;
SELECT 
    @datetime AS "Original Value",
    @datetime2 AS "Converted Value";

Result:

Original Value	         Converted Value
-----------------------  ---------------------------
2035-12-30 22:34:43.127	 2035-12-30 22:34:43.1266667

In this case we assigned a datetime value to a datetime2 variable. SQL Server implicitly converted the datetime value to datetime2 before assigning the value. The result is a datetime2 value.

Convert Date Format

We can also convert date formats. For example, we could change a date’s format from mm/dd/yyyy to dd/mm/yyyy.

This is where CONVERT() has extra functionality over CAST(). While CAST() is standards compliant, it doesn’t allow us to change the date format. CONVERT() on the other hand, is not standards compliant, but it does allow us to specify a style for the translated date.

Example:

SELECT 
    GETDATE() AS "Original Value",
    CONVERT( nvarchar, GETDATE(), 107 ) AS "Converted Value";

Result:

Original Value	         Converted Value
-----------------------  ---------------
2023-01-08 05:42:56.727	 Jan 08, 2023

Here I used 107 as the date style of the resulting date. See List of Date Formats Available with CONVERT() in SQL Server for a list of styles we can use when formatting dates with this function.

When we format dates like this, we need to convert them to string data, otherwise we will probably find that the outputted date is not in the format we specified. That’s why I converted it to nvarchar in this example.

Another option is to use the FORMAT() function. This function has extra functionality that allows us to specify a locale to use for the date format. This can be handy, because we don’t need to remember which format to use. Instead, we specify the locale, and SQL Server will work out which format to use for that locale. See How to Format the Date & Time in SQL Server for examples.

Convert a String Literal to a Date

In the above example we converted a datetime value to an nvarchar value, which is a string value. This shows that we can convert date values to non-date data types. We can also convert the other way around – from a non-date data type to a date data type.

Here’s an example of explicitly converting a string literal to a date:

SELECT CAST('25 Dec 2035' AS date);

Result:

2035-12-25

In this case, SQL Server was able to work out which date it was. Sometimes it might have trouble. If we get an error, we can try using the PARSE() function instead:

SELECT PARSE('Thursday, 20 Dec 2018' AS datetime2);

Result:

2018-12-20 00:00:00.0000000

See 6 Ways to Convert a String to a Date/Time Value in SQL Server for more examples and other options.

Convert a Time to a Date

We can convert time values to date data types even though time values don’t contain a date portion. When we do this, the date is set to 1900-01-01 (unless it happens to get rounded up to 1900-01-02 due to the fractional seconds and precision).

Here’s an example of converting a time value to a datetime2 value:

DECLARE @thetime time;
SET @thetime = '23:15:59.004007';
SELECT 
  @thetime AS 'time',
  CAST(@thetime AS datetime2) AS 'datetime2';

Result:

time	                datetime2
----------------------  ---------------------------
23:15:59.0040070	1900-01-01 23:15:59.0040070

See Convert time to datetime2 in SQL Server for more examples and information on doing this.

Also see the bottom of this article for links to more articles that provide examples of converting between date and time values.

Convert a Number to a Date

Trying to convert a number to a date can be tricky and may not always work. It all depends on the number and what exactly it is that we’re trying to do. That said, here are a few examples of converting numbers to dates.

Convert the Number of Days Since 1900-01-01 to datetime

In this example we convert the number of days since 1900-01-01 00:00:00.000 to a datetime value:

SELECT CAST(47245.5278 AS datetime);

Result:

2029-05-09 12:40:01.920

We can also convert it implicitly:

DECLARE @number float = 47245.5278;
DECLARE @datetime datetime = @number;
SELECT 
    @number AS "Original Value",
    @datetime AS "Converted Value";

Result:

Original Value	Converted Value
--------------  -----------------------
47245.528	2029-05-09 12:40:01.920

In this case I implicitly converted a float to a datetime. The float value represents the number of days since 1900-01-01 00:00:00.000. The fractional part specifies the time part.

Convert an Integer Date to a date Type

In this example the number already resembles a date, but without the hyphens:

SELECT CAST( CAST( 20281030 AS char(8)) AS date );

Result:

2028-10-30

In this case I used CAST() to convert the number to a char(8), then I used another CAST() to convert that char(8) to a date.

Convert a Unix Timestamp to a datetime Value

If the number is a Unix timestamp, we can use SQL Server’s DATEADD() function to convert it to a datetime value:

SELECT DATEADD(s, 1860935119, '1970-01-01');

Result:

2028-12-20 14:25:19.000

The Unix timestamp is the number of seconds that have elapsed since 1970-01-01 00:00:00. In this case, the Unix timestamp was 1860935119, which translated into a date and time of 2028-12-20 14:25:19.000.

This was an implicit conversion, and the result was a datetime value. This is because DATEADD() returns a datetime whenever the date argument is a string literal date, and that is the case here. If the date argument is another data type, the function returns that data type.

See How to Convert a Unix Timestamp to a Date/Time Value in SQL Server for more examples.

Allowed Data Conversions

Some data types can’t be converted to other types. The following chart shows which data conversions are allowed and whether they can be done implicitly or explicitly.

Source: Microsoft

The above chart only includes SQL Server system-supplied data types. It doesn’t include user-defined data types.

Data Type Precedence

When an operator combines expressions of different data types, the data type with the lower precedence is first converted to the data type with the higher precedence.

SQL Server uses the following precedence order for data type conversions:

1. user-defined data types (highest)
2. sql_variant
3. xml
4. datetimeoffset
5. datetime2
6. datetime
7. smalldatetime
8. date
9. time
10. float
11. real
12. decimal
13. money
14. smallmoney
15. bigint
16. int
17. smallint
18. tinyint
19. bit
20. ntext
21. text
22. image
23. timestamp
24. uniqueidentifier
25. nvarchar (including nvarchar(max) )
26. nchar
27. varchar (including varchar(max) )
28. char
29. varbinary (including varbinary(max) )
30. binary (lowest)

Source: Microsoft

Also see my article Data Type Precedence in SQL Server for examples.

Verify a Conversion

We can use the sys.dm_exec_describe_first_result_set system dynamic management function to check the data type of each column of a query result. We can therefore use it to check the resulting data type of a conversion.

Example:

SELECT 
    name AS "Column Name",
    system_type_name,
    max_length,
    [precision],
    scale
FROM sys.dm_exec_describe_first_result_set(
    'SELECT 
    GETDATE() AS "Original Value",
    CAST( GETDATE() AS date ) AS "Converted Value";', 
    null, 
    0);

Result:

Column Name	    system_type_name	max_length	precision	scale
------------------  ------------------  --------------  --------------  -----
Original Value	    datetime	        8	        23	        3
Converted Value	    date	        3	        10	        0

Here I passed my query to the sys.dm_exec_describe_first_result_set system dynamic management function to tell me the data types of the two columns returned by that query. It replied with datetime and date as expected.

See How sys.dm_exec_describe_first_result_set Works in SQL Server for more information about this function works.

Will the Value be Modified During the Conversion?

Sometimes when converting between data types, the value is modified in order to fit within the constraints of the new data type. For example, converting a datetime to date will remove the time component from the original value, and converting time to datetime2 will add a date portion.

Having the time component removed or the date component added is quite an obvious change, and we would only do it if that was our intention. And if it wasn’t our intention, it would be pretty easy to spot the mistake. But some modifications can be much more subtle.

Consider the following example:

DECLARE @datetime datetime = '2035-12-30 22:34:43.125';
SELECT 
    @datetime AS "datetime",
    CONVERT( datetime2(7), @datetime ) AS "datetime2";

Result:

datetime	         datetime2
-----------------------  ---------------------------
2035-12-30 22:34:43.127	 2035-12-30 22:34:43.1266667

In this case we explicitly converted a datetime value to a datetime2 value.

With the datetime data type, values are rounded to increments of .000, .003, or .007 seconds. That’s why our fractional seconds of .125 ended up as .127 for the datetime value.

The datetime2 value supports a fractional seconds precision of 7 and converted this to .1266667. If we had assigned the original string literal directly to the datetime2 value, it would be .1250000.

The datetime2 data type supports a user-defined fractional seconds precision of up to seven fractional seconds (datetime doesn’t). Seven is the default precision, but in the above example I explicitly specified it anyway by appending (7).

Let’s change the precision of the datetime2 value:

DECLARE @datetime datetime = '2035-12-30 22:34:43.125';
SELECT 
    @datetime AS "datetime",
    CONVERT( datetime2(2), @datetime ) AS "datetime2";

Result:

datetime	         datetime2
-----------------------  ---------------------------
2035-12-30 22:34:43.127	 2035-12-30 22:34:43.13

This time the fractional seconds have been rounded up to .13.

We should always try to consider factors like this when we convert dates (and other data types for that matter).

Converting Between Different Date/Time Values

Click on the following links to see examples of converting between different date/time values.

Convert from datetime

• Convert datetime to datetime2
• Convert datetime to datetimeoffset
• Convert datetime to smalldatetime
• Convert datetime to date
• Convert datetime to time

Convert from datetime2

• Convert datetime2 to datetime
• Convert datetime2 to datetimeoffset
• Convert datetime2 to smalldatetime
• Convert datetime2 to date
• Convert datetime2 to time

Convert from datetimeoffset

• Convert datetimeoffset to datetime
• Convert datetimeoffset to datetime2
• Convert datetimeoffset to smalldatetime
• Convert datetimeoffset to date
• Convert datetimeoffset to time

Convert from smalldatetime

• Convert smalldatetime to datetime
• Convert smalldatetime to datetime2
• Convert smalldatetime to datetimeoffset
• Convert smalldatetime to date
• Convert smalldatetime to time

Convert from date

• Convert date to datetime
• Convert date to datetime2
• Convert date to smalldatetime
• Convert date to datetimeoffset

Convert from time

• Convert time to datetime
• Convert time to datetime2
• Convert time to smalldatetime
• Convert time to datetimeoffset