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.
I have two problems to address
- Convert the float to datetime
- 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)
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.