4

Looking through a server built by someone else, I noticed the time is stored in int format. By inserting some data, I have been able to do the following deduction:

285571 corresponds to 6:51:00am (plus minus two seconds)

For those who ask, the date is stored in days since 1/1/1900 in another column.

The question is: What's the magic formula used for the time conversion?

Thank you.

Shnugo
  • 66,100
  • 9
  • 53
  • 114
taylorswiftfan
  • 1,371
  • 21
  • 35

1 Answers1

6

Try this:

DECLARE @t DATETIME='06:51:00';
SELECT CAST(@t AS FLOAT)

The result

0,285416666666667

Your ridiculous value seems to be the part after the comma, meaning the fraction of a day. You could say After 28.54 percent of a full day it is 06:51

EDIT

Thx to Martin Smith I have to correct my "DATETIME is a FLOAT".

A DATETIME is stored as two 4-byte-integers combined to an 8-byte-structure actually. The first int is taken as a day's count, the second is a counter of ticks since midnight. You might read this.

Another common representation (I think this is valid for VB(A), Access and related systems) is a datetime as float. The integer part is the day's count and the fraction is the day's fraction. SQL Server converts this implicitly:

SELECT CAST(0.5 AS DATETIME)

The result is 1900-01-01 12:00:00.000

To get your time (the magic formula) you have to do this:

SELECT CAST(0.285571 AS DATETIME)

UPDATE the magic solution

DECLARE @t INT=285571;
SELECT CAST(CAST('0.' + CAST(@t AS VARCHAR(100)) AS FLOAT) AS DATETIME)

You might want to add one more CAST( ... AS TIME) (depending on your SQL Server's version)

And for the fans of pure calculations

SELECT CAST(CAST(CAST(@t AS FLOAT)/CAST(POWER(10,CAST(LOG10(@t) AS INT)+1) AS FLOAT) AS DATETIME) AS TIME)

UPDATE

In your case it might be best to combine your day-counter and your time-value with a . on string-level, cast this to float and the result to datetime.

Community
  • 1
  • 1
Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • RE: "A DATETIME is a FLOAT actually." A datetime isn't a float even though you can cast it to one. It is stored as two integers. One with days since an offset date and one with number of ticks in the day. – Martin Smith Aug 13 '16 at 22:37
  • 1
    @MartinSmith Thank you for this hint! I read some articles about the internal representation of `DATETIME` and edited my answer. – Shnugo Aug 13 '16 at 23:05
  • Plus1 Great insight! I'm ashamed to admit my thought process when I first saw the question. I find it odd that one can't convert a TIME to float to get the % of the day. – John Cappelletti Aug 14 '16 at 09:25