Printable View
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.
right 5 digits julian date, first three are the interval maybe? I'm lost ..
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 ????
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.
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,
[VBA]
#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
[/VBA]
I got
oops... forgot to add, in Excel I got
aha! the five hour difference is the GMT Offset, as the function calculates UNIX Time.Quote:
Originally Posted by stanl
Apologies - I did arrive to the same conclusion.
Wow - what a crazy way to store dates.