Search code examples
sqldatetimecastinginteger

Convert INT to DATETIME (SQL)

I am trying to convert a date to datetime but am getting errors. The datatype I'm converting from is (float,null) and I'd like to convert it to DATETIME.

The first line of this code works fine, but I get this error on the second line:

Arithmetic overflow error converting expression to data type datetime.

CAST(CAST( rnwl_efctv_dt AS INT) AS char(8)),
CAST(CAST( rnwl_efctv_dt AS INT) AS DATETIME),
Solution

  • you need to convert to char first because converting to int adds those days to 1900-01-01

    select CONVERT (datetime,convert(char(8),rnwl_efctv_dt ))

    here are some examples

    select CONVERT (datetime,5)

    1900-01-06 00:00:00.000

    select CONVERT (datetime,20100101)

    blows up, because you can't add 20100101 days to 1900-01-01..you go above the limit

    convert to char first 

    declare @i int
select @i = 20100101
select CONVERT (datetime,convert(char(8),@i))