asked
epoch datetime conversion
Hi I have a date field with column values like 1315026043000, 1315042131000, 1315042432000, 1315042588000, 1315646427000, 1315646861000 which are count in seconds from 01/01/1970. I am doing datediff like this select DATEDIFF(SECOND, '19700101', date_column) as fdate from tbl_name; but I get an error saying: Arithmetic overflow error converting expression to data type datetime. Whats the correct way of doing this? Thanks Priyank
Your DATEDIFF isn't working because date_col isn't a date value. DATEADD(second, date_col, '19700101') is what you need but that fails with such big numbers because they are too big for the second parameter, which is an INT. So I started playing with the idea of splitting the problem into adding smaller numbers of different intervals to get the answer. There are 86,400 seconds in a day so you could: 1) Divide date_col by 86400, discard the remainder, and add that many days and then 2) Use date_col modulus 86400 to get the remaining seconds and add that. This seems to work. This code `select 1315026043000 as a , (floor(1315026043000 / 86400) * 86400) + (1315026043000 % 86400) as b` returns `1315026043000, 1315026043000` So I developed it into this code `select dateadd(second, (1315026043000 % 86400), dateadd(day, floor(1315026043000 / 86400), '19700101'))` but this returns an error Msg 517, Level 16, State 1, Line 1 Adding a value to a 'datetime' column caused an overflow. The problem is basically that 1315026043000 / 86400 = 15220208, which is about 42,000 years after 01/01/1970! So I don't think those numbers are seconds. They are probably milliseconds so you just need to develop this method accordingly once you know what the units are.
