Consulting

Results 1 to 8 of 8

Thread: Solved: 8 decimal date field?

  1. #1
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    221
    Location

    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. #2
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    221
    Location
    right 5 digits julian date, first three are the interval maybe? I'm lost ..

  3. #3
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    221
    Location
    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. #4
    VBAX Tutor Mavyak's Avatar
    Joined
    Jul 2008
    Posts
    204
    Location
    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.
    Last edited by Mavyak; 09-10-2008 at 07:41 PM.

  5. #5
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    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

  6. #6
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    oops... forgot to add, in Excel I got

  7. #7
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    Quote 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. #8
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    221
    Location
    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
  •