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.

Try using Convert instead. msdn.microsoft.com/en-us/library/ms187928.aspx — Neo
– Neo, Commented Nov 16, 2016 at 15:54
That was the answer on a similar question on SO. Thought I would try it. Already tried a regular "Cast" but to no avail — Adit2789
– Adit2789, Commented Nov 16, 2016 at 16:43

Ben Osborne · Accepted Answer · 2016-11-16 15:58:17Z

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;

answered Nov 16, 2016 at 15:58

Ben Osborne

1,57213 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Adit2789 Over a year ago

Thanks a lot. After running your code I realized that there is one row in my table that has a date of 0 for some reason. This was what was likely throwing up the error. Your code helped me realize that

Ben Osborne Over a year ago

I'm glad you got it figured out.

Cato Over a year ago

@Adit2789 - I think what the problem was that got fixed here, is that a date switches to float based on the number of days since the start of 1900, with the part after the decimal point representing time - your format is more like a date string changed into a number - it is actually about the year 55000 - beyond SQLs 9999 limit -

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related