sql-servert-sqldatetimebigintsql-convert

How to convert numeric number into datatime

I need help to convert a bigint value into a datetime value using SQL Server.
Example value 634254092190100000

How to convert to datetime value?

So far this is what I have but getting an error:

Arithmetic overflow error converting expression to data type int

select dateadd(s, convert(bigint, 632979854880200000) / 1000, convert(datetime, '1-1-1970 00:00:00'))
Solution

  • Please do :

    SELECT CAST((YOURVALUE- 599266080000000000) / 864000000000 AS datetime)

    For exemple :

    SELECT CAST((635307578922100000 - 599266080000000000) / 864000000000 AS datetime)

    Gives "2014-03-18 16:44:52"

    Knowing that :
    - 599266080000000000 is 19000101
    - 864000000000 is the number of ms / day
    - 599266080000000000 / 864000000000 = 693595 /365 = 1900 (aprox)

    Please see https://learn.microsoft.com/fr-fr/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql for further details about starting day for each datetime type family...