Using T-SQL To Validate and Convert a FLOAT to DATETIME

The Problem: datetime stored as a float

You don't always get to choose your production software.  Anytime a professional software product stores a date as a float it seems like someone should get a good scolding.  There are rare occasions where a float is a necessary design decision, but that doesn't make it mean you need to enjoy working with that database.    

When an application stores date information as a float it adds a level of complexity to otherwise standard querying -- one of the most confusing data types in SQL Server is datetime. This confusion leads to interesting conversations, as solutions involving datetime require specific attention to detail.    

T-SQL handleds datetime strange, some of these issues are solved with the use of datetime2; however, certain limitations in my production enviroment made the use of datetime more appropriate.  

The Solution

I have two problems to address

1. Convert the float to datetime
2. Ensure the data is semi-validated

Using a simple cast will sometimes toss an arithmetic overflow.  Also, datetime is a strange beast, prone to fussy complications.  To better address this, I took specific steps to ensure that the data picked apart and put together exactly as SQL Server expects datetime to be formed: 1900-01-01T00:01:00.000 (yyyy-mm-ddThh:mm:ss.mmm)

SELECT 
CASE 
    WHEN (LEN(FLOOR(CAST(Float_Date AS BIGINT))) = 14) 
        THEN CONVERT(datetime, 
        (  
        LEFT(CAST(CAST(Float_Date as BigInt) as varchar(14)),4)+'-'+
        SUBSTRING(CAST(CAST(Float_Date as BigInt) as varchar(14)),5,2)+'-'+
        SUBSTRING(CAST(CAST(Float_Date as BigInt) as varchar(14)),7,2)+'T'+
        SUBSTRING(CAST(CAST(Float_Date as BigInt) as varchar(14)),9,2)+':'+
        SUBSTRING(CAST(CAST(Float_Date as BigInt) as varchar(14)),11,2)+':'+
        SUBSTRING(CAST(CAST(Float_Date as BigInt) as varchar(14)),13,2)+'.000'
        )) 
    ELSE NULL 
END AS Real_Date
FROM dbo.ISSUES AS i  

To explain how this works, review the pseudo code below:    

Is the Float_Date field 14 numbers long? 
    YES:
        Take the first 4 characters as year 
        Add a '-'
        Then grab the next 2 characters as MM (Months)
        Add a '-'
        Then grab the next 2 characters as DD (Days)
        Then denote the time by adding 'T'
        Then grab the next 2 characters as HH (Hours)
        Add a ':'
        Then grab the next 2 characters as MM (Minutes)
        Add a ':'
        Then grab the next 2 characters as SS (Seconds)
        Then add '.000' as milliseconds

        Convert this as datetime 
    
    NO: Set it as null. 

For more information, check out MSDN.