PDA

View Full Version : Solved: 8 decimal date field?



debauch
09-02-2008, 05:59 PM
Hi,

I am currently working in an oracle database that has the dates listed as 8 digits (as a decimal data type). How in the world can I convert this to something that makes sense?

Examples I pulled out is as follows :
57336750
56757900

If it helps, it's an interval based table.

debauch
09-02-2008, 06:38 PM
right 5 digits julian date, first three are the interval maybe? I'm lost ..

debauch
09-10-2008, 04:04 PM
Ok,
So I found out it's COleDateTime. IF you take the number and divde by 1400, it gives you the date in the same format as if you were to take a date in excel and format it as general. I think it may also be known as serial date. I believe it's the number of mintes/seconds from 1/1/1900 forward ....

Any ideas how to convert this to a readable date via SQL ????

Mavyak
09-10-2008, 07:16 PM
It is beyond me to explain to you how to do it but since COleDateTime is a C++ data type, I think your solution will be to create a CLR assembly using C++ or C# that contains a function to do the conversion and return the result in an SQL Server acceptable data type. I haven't played with C++ in close to a decade and only taught myself C# a few months ago. I taught myself C# so I could take advantage of creating CLR assemblies containing functions that allowed me to process comma separated values in fields as an array of values. I don't envy you. Hopefully someone can come up with a mathematical way of doing what you need instead.

Edit: My solution was for SQL Server. I just re-read your post and now realize you're using Oracle. I'm not sure how to solve your problem now but will continue to research it and will post back if I come up with anything.

stanl
09-14-2008, 12:57 PM
I think you divide by 1440 not 1400. Not sure if the code below can be converted to VBA, but when I passed your number

56757900 / 1440

to the following Winbatch UDF,


#DefineFunction udfFloatToYmdHms(fDateTime)
;courtesy of Detlev Dalitz
iDayAdjust=693960
iSecondsPerDay=86400
iDay=Floor(fDateTime)
fFraction=fDateTime-iDay
iDay=iDay+iDayAdjust
iSeconds=Int(fFraction*iSecondsPerDay)
sYmdHms=TimeJulToYmd(iDay)
sYmdHms=TimeAdd(sYmdHms,StrCat("0:0:0:0:0:",iSeconds)) ; make valid datetime string
Return (sYmdHms)
#EndFunction



I got

stanl
09-14-2008, 01:04 PM
oops... forgot to add, in Excel I got

stanl
09-16-2008, 10:12 AM
oops... forgot to add, in Excel I got

aha! the five hour difference is the GMT Offset, as the function calculates UNIX Time.

debauch
10-07-2008, 06:02 PM
Apologies - I did arrive to the same conclusion.

Wow - what a crazy way to store dates.