Float column used as a date. Contains negative number

Question

Post reply

Float column used as a date. Contains negative number

mbutler 70704

SSC Rookie

Points: 40
More actions
December 3, 2010 at 2:43 pm

#144319

I have a third party table with a column DateOfBirth. An example of the columns contents are:
-1310389200000
-1792155600000
983620800000
I've tried many different methods to convert this into a meaningful date.
Any insight would be appreciated.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

That's hashed, encrypted, or proprietary... and rather wild. FLOAT?!

Any chance you can share the name of the software?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

mbutler 70704 SSC Rookie Points: 40 More actions · Answer 2

Better not share the name of the software. I've been searching and maybe it's stored as some type of windows file time?

mbutler 70704 SSC Rookie Points: 40 More actions · Answer 3

This is what I have so far:

declare @floatdatetime as float

set @floatdatetime = -1310389200000

select DATEADD(ms,

((cast(@floatdatetime as bigint)) / CAST(10000 AS bigint)) % 86400000,

DATEADD(day, (cast (@floatdatetime as bigint)) / CAST(864000000000 AS bigint) - 109207, 0))

Alas, this produces a "Adding a value to a 'datetime' column caused overflow" error.

Revenant SSC-Forever Points: 42467 More actions · Answer 4

They look to me like UNIX timestamps ('epochtime'). Thery even make sense for negative number for people born befor January 1, 1970. For example, the last number divided by 1,000 (to get rid of milliseconds) translates to 2/12/1973.

mbutler 70704 SSC Rookie Points: 40 More actions · Answer 5

Thanks for your help. You identifying the type of data I'm dealing with put me on the right track to finding the solution.

Here's what works for me:

SELECT DATEADD(s,(-1310389200000/1000),'19700101')