0

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'))
Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
Jason312
  • 197
  • 1
  • 1
  • 10

1 Answers1

2

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...

Stéphane CLEMENT
  • 374
  • 1
  • 14
  • 24 hours/day * 60 minutes/hour * 60 seconds/minute * 1000 milliseconds/second = 86400000 milliseconds/day by my reckoning. The OP used a base date of 1970-01-01 which you don't seem to account for. At least the OP is happy with the answer. – HABO Sep 29 '17 at 16:11
  • I know, I told the OP about that... But With 19700101, I got out of range message... Meaning the OP was wrong about that (too) : the bigint date value starts from 1900 and not from 1970. – Stéphane CLEMENT Sep 29 '17 at 16:19