CODEWITHSUNDEEP
oracle-databasedatenumbers
user2724502
user2724502

Reputation: 299

Date and time stored as NUMBER fields in Oracle - how to convert to DATE...?

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

Answers (3)

user2724502
user2724502

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

GTG
GTG

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

dcieslak
dcieslak

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

Related Questions