Convert int to time (weird values)

Question

Looking through a server created by someone else, I noticed that the time is stored in int format. Having inserted some data, I was able to draw the following conclusion:

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

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

Question: What magic formula is used to transform time?

Thank.

Answer 1

Try the following:

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

Result

0,285416666666667

Your ridiculous value seems to be a decimal place, which means a fraction of the day. We can say that after 28.54 percent of the full day, this is 06:51

EDIT

. "DATETIME - FLOAT".

A DATETIME 4- , 8- . int , - ticks . .

( , VB (A), Access ) DATETIME float. - , - . SQL Server :

SELECT CAST(0.5 AS DATETIME)

1900-01-01 12:00:00.000

( ), :

SELECT CAST(0.285571 AS DATETIME)

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

, CAST( ... AS TIME) ( SQL Server)

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

UPDATE

. , float, - DATETIME.