UTC date from an integer

Question

UTC date from an integer

stricknyn

SSCertifiable

Points: 6560
More actions
December 29, 2014 at 2:27 pm

#302061

Hi all,
I'm trying to get the UTC date from an integer. See my SQL below:
Declare @unix_date bigint
Set @unix_date = 3499288964
SELECT dateadd(day, @unix_date/(86400), dateadd(second, @unix_date
% (86400), '19700101'))
It returns '2080-11-20 00:42:44.000'. This is dead on except for the year (which should be 2014). I was thinking maybe my bigint value of 3499288964 was milliseconds or microseconds so I adjusted the seconds value (86400) in the select statement to reflect milliseconds (86400000)
with no success and microseconds (86400000000) with no success as both of those gave incorrect results. Closest I got was with the seconds (86400) which of course returns the incorrect year. Can anyone see what I'm doing wrong?
Thanks,
Strick

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

It seems likely that your assumption about the meaning of the value for @unix_date is where the problem lies, and it appears that seconds or milliseconds or microseconds just isn't right. Try googling the units used for unix date/time values and see what you find. I expect you'll find your answer there. The one time I remember hearing about Unix date/time values, I seem to recall a mention of ticks, which are not simply some power of 10 derivation from seconds. I just don't recall the details...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

ScottPletcher SSC Guru Points: 101092 More actions · Answer 2

Looks to me like they used 1904 as the base year rather than 1970. That would be quite logical if they needed to show dates before 1970, such as birthdates, etc..

Declare @unix_date bigint

Set @unix_date = 3499288964

SELECT dateadd(day, @unix_date/(86400), dateadd(second, @unix_date

% (86400), '19040101'))

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".