# Thread: Solved: 8 decimal date field?

1. ## Solved: 8 decimal date field?

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.

2. right 5 digits julian date, first three are the interval maybe? I'm lost ..

3. 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 ????

4. 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.

5. 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
iSecondsPerDay=86400
iDay=Floor(fDateTime)
fFraction=fDateTime-iDay
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

6. oops... forgot to add, in Excel I got

7. Originally Posted by stanl
oops... forgot to add, in Excel I got
aha! the five hour difference is the GMT Offset, as the function calculates UNIX Time.

8. Apologies - I did arrive to the same conclusion.

Wow - what a crazy way to store dates.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•