Reputation: 299
I'm working with a database that has call records in it and which stores the date and time for each call in two separate NUMBER fields.
I can't work out how the number stored in the fields relates to the date and time that's shown on the screen when viewing the record through the client app. I've tried all the usual Julian-related stuff, number of minutes/seconds since 1970, etc but don't seem to be able to figure it out.
For a date of 08/07/2015 and a time of 14:32:45, the two fields contain the following values:
Can anyone shed any light on how to get from those numbers to the correct date/time?
Many thanks...
Upvotes: 1
Views: 926
Reputation: 299
OK, this is a partial answer...I've found out how to do the date conversion:
Year = TRUNC(date_num / 65536)
Month = TRUNC((date_num - (Year * 65536)) / 256
Day = date_num - (Year * 65536) - (Month * 256)
So for the original example:
TRUNC(132056840 / 65536) = 2015
TRUNC((132056840 - (2015 * 65536)) / 256 = 7
132056840 - (2015 * 65536) - (7 * 256) = 8
Why they couldn't just use a DATE field, I really don't know!!!
Just need to try and figure out how to calculate the time now...
Upvotes: 1
Reputation: 4954
This seems to be a kind of hexadecimal magic being done.
236989696 in decimal numbers = E202D00 in HEX = E:20:2D:00 = 14:32:45:00
132056840 in decimal numbers = 7DF0708 in HEX = 7DF-07-08 = 2015-07-08
I'm not sure what is the easiest way to convert this to a string, that would depend on if you want to let the Oracle database do that or you would rather do that in your data layer.
Upvotes: 3
Reputation: 2715
You can try to search store procedures for these columns/tables. Maybe application encodes it there. I guess it's worth of try.
SELECT *
FROM user_source
Upvotes: 1