Convert Float to DateTime

Question

Post reply

Convert Float to DateTime

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
January 15, 2014 at 7:30 am

#302647

Hello Everyone
I have got a real issue with some really horrible data that came out of Oracle. They were using float data type to store a datetime value. I was given some definition of how to convert this, but it does not seem to be working.
Data:
1.21793287400577E+15
Definition:
•The date/time stamp portion of the ID is actually the number of seconds since
January 1, 1960
•The number is 16 digits, the last six of which represent a workstation ID
So I tried removing the last 6 digits and then performing a cast or convert, which neither seem to work.
I would greatly appreciate any assistance with this if you have any experience working or dealing with this.
Thank you in advance for all your time, assistance and code samples.
Andrew SQLDBA

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

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

David McKinney SSChampion Points: 10472 More actions · Answer 1

just going from your definition, I pasted the number into Excel

which gave me

1217932874005770

I then removed the last 6 digits which gave me

1217932874

and finally did a dateadd

SELECT dateadd(s,1217932874,'1 jan 1960')

which gave me

1998-08-05 10:41:14.000

...don't know if it's the answer you were expecting!?

Does this help?

David McKinney SSChampion Points: 10472 More actions · Answer 2

SELECT dateadd(s,cast(1.21793287400577E+15 AS bigint) / 1000000,'1 jan 1960')

Abu Dina SSChampion Points: 14155 More actions · Answer 3

Nice 1 David:

This is my attempt lol:

declare @test-2 float = '1.21793287400577E+15'

select left(cast(CAST(@test as bigint) as varchar(20)), LEN(cast(CAST(@test as bigint) as varchar(20)))-6)

select DATEADD(SS, CAST(left(cast(CAST(@test as bigint) as varchar(20)), LEN(cast(CAST(@test as bigint) as varchar(20)))-6) AS INT), '1960-01-01')

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

David McKinney SSChampion Points: 10472 More actions · Answer 4

Abu Dina (1/15/2014)
Nice 1 David:
This is my attempt lol:
declare @test-2 float = '1.21793287400577E+15'
select left(cast(CAST(@test as bigint) as varchar(20)), LEN(cast(CAST(@test as bigint) as varchar(20)))-6)
select DATEADD(SS, CAST(left(cast(CAST(@test as bigint) as varchar(20)), LEN(cast(CAST(@test as bigint) as varchar(20)))-6) AS INT), '1960-01-01')

...well, we get the same answer, which is encouraging 😛

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 5

Thanks Everyone

I was getting close, but I was struggling. Thank you very much for your assistance.

Who ever had the bright idea of storing date data like this needs to have their *ss removed.

😀

Andrew SQLDBA