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

Answer 1

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