Arithmetic overflow error when converting float to datetime (SQL Server 2014)

Question

I have a column that has dates as float data type. They are in a YYYYMMDD format. I need to change these dates back into a datetime format.

I tried doing the following:

Select top 1 Date_col,CAST(FLOOR((CAST(Date_col AS numeric(10,10)))) AS DATETIME)  from dbo.table_2016

All I need is to convert the data type, but I would like to retain the formatting if possible.

Answer 1

Try this:

select
    DATEFROMPARTS(  
        left(convert(char(8), convert(integer, Date_Col)), 4),
        substring(convert(char(8), convert(integer, Date_Col)), 5, 2),
        substring(convert(char(8), convert(integer, Date_Col)), 7, 2))
from
    table_2016;