Adit2789 Adit2789 - 1 year ago 105
SQL Question

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

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 Source

Try this:

        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))
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download