Trying to convert a number to a date in SQL Server 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 some scenarios where we can convert a number to a date value.

Convert an Integer Date to a date Type

In this example the number is in yyyymmdd format, which already resembles a date, but without the hyphens.

We can use a function like CAST() or CONVERT() to convert that number to a date type:

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.

It didn’t necessarily have to be a date type. I could have converted it to another date/time value such as datetime or datetime2. In such cases, a time component would be added, set to all zeros (e.g. 00:00:00.0000000 for datetime2).

If the integer represents a different value, such as a Unix timestamp or the number of days since 1900-01-01, take a look at the following examples.

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, 1870917359, '1970-01-01');

Result:

2029-04-15 03:15:59.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 1870917359, which translated into a date and time of 2029-04-15 03:15:59.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.

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

If we’re given a floating point number that represents the number of days since 1900-01-01, we can convert that into a datetime value with functions like CAST() and CONVERT().

Example:

SELECT CAST(49723.3986 AS datetime);

Result:

2036-02-20 09:33:59.040

We can also convert it implicitly:

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

Result:

Original Value	Converted Value
--------------  -----------------------
49723.3986	2036-02-20 09:33:59.040

In this case I implicitly converted a float to a datetime.