Error when converting float to datetime

Question

I am trying to convert a float in the format yyyymmdd to datetime. According to this the correct style code for that format is 112.

Code:

select
    convert(datetime,cast(myDate as numeric),112)
from MyTable

Error:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

I get the same error without the cast as numeric part. I've been looking around for a couple hours, but I haven't been able to find anything that fixes this. If you know a better way to convert the float to a datetime I would be open to that idea.

Thank you for your help.

EDIT
Here is the working code:

SELECT
    case when isdate(CAST(CAST(myDate AS INT) AS VARCHAR(8))) = 1
            then CAST(CAST(CAST(myDate AS INT) AS VARCHAR(8)) AS DATETIME)
        end
from MyTable

I wrapped it in the isdate because there were a few invalid dates in there. Thanks to Matt for the help.

EDIT2
Better version:

SELECT
    TRY_CAST(CAST(CAST(myDate AS INT) AS VARCHAR(8)) AS DATETIME)
FROM MyTable

How do you know that you don't have a value like 20140230 in myDate field? — Indi
– Indi, Commented Jul 21, 2014 at 14:11
The "112" designates the format of the output value, not the input value. — Matt
– Matt, Commented Jul 21, 2014 at 14:12
Further, you will notice that the date format code is used to convert DATETIME to VARCHAR, not FLOAT to DATETIME. — Matt
– Matt, Commented Jul 21, 2014 at 14:15
I wasn't the one who decided to store dates as floats. I just have to deal with it. — Pat
– Pat, Commented Jul 21, 2014 at 14:20

Matt · Accepted Answer · 2014-07-21 14:10:45Z

8

First you must convert the FLOAT to a VARCHAR. And since FLOAT has a number of decimal points, it must first be converted to an INT.

DECLARE @myDate FLOAT
SET @myDate = 20140721
SELECT CAST(CAST(@myDate AS INT) AS VARCHAR(8))
--20140721

Then you can convert the VARCHAR to DATE or DATETIME format.

DECLARE @myDate FLOAT
SET @myDate = 20140721
SELECT CAST(CAST(CAST(@myDate AS INT) AS VARCHAR(8)) AS DATE)
--2014-07-21

answered Jul 21, 2014 at 14:10

Matt

1,16513 silver badges29 bronze badges

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

3 Comments

Pat Over a year ago

Thanks! I had a few invalid dates, so I had wrapped it in a an isdate, but this works great.

Pat Over a year ago

Thanks Matt. I didn't know about TRY_CAST. That is a great thing to have.

Matt Over a year ago

Yeah, that TRY_CAST really cleans up the code nicely in your solution!

Collectives™ on Stack Overflow

Error when converting float to datetime

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