Translating 12 byte Ingres date into Oracle 2

[SJSFoxPro]

I have a requirement to load data from an external source, via a third party, into our Oracle database. The file includes date columns in a 12 byte format that was extracted from an Ingres database (of which I have little information). I am told that the 12 byte date format is the Ingres date type.

Is there anyone out there that has crossed this bridge? Ingres dates to Oracle?

Any direction would be helpful.

 

[SantaMufasa]

SJS,

If a DATE expression does not match (exactly) Oracle's internal DATE representations (which Ingres definitely does not), then here are a couple of methods to transform the dates into Oracle-readable terms:[ul][li]Pre-process the incoming data to reformat the 12-byte Ingres DATEs into any character representation of DATE and TIME. Once you have the Ingres DATE in some character format, you can then use the Oracle TO_DATE() function to interpret the character representation using the myriad of date masks available to you for the TO_DATE function. (If you can take care of getting the Ingres DATEs in a character format, then we certainly can help you with the TO_DATE masks if you are not familiar with them.)[/li][li]Write either an Oracle function or some non-Oracle (e.g., C, C++, Java, et cetera) callable function that uses the Ingres DATE expression as an incoming argument and RETURNs a valid Oracle DATE expression. (This solution depends upon your Googling for the internal parsing/meanings of the 12-byte Ingres DATE expressions. Any transformation function depends upon understanding the meaning of "The 12 Bytes".[/li][/ul]
Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[taupirho]

I used to work with Ingres. I did some investigation once into the date format and came up with this. Note it was some time ago (Version 6.4) and things may have changed in the intervening timeframe but it may be of help.

Value of Bytes 1 and 2 Value of Bytes 9-12 Meaning
---------------------- --------------------- -----------
8704 60000 Interval Only (1 min)
8704 1000 Interval Only (1 sec)
8704 3600000 Interval Only (1 hour)
4608 0 Interval Only (1 day)
2560 0 Interval Only (1 month)
1536 0 Interval Only (1 year)
256 0 dd/mm/yy (No time)
0 0 Blank Date
8448 (See below) dd/mm/yy hh:mm:ss


Bytes 1 and 2 equate to integer 0 if the data is blank, non-zero otherwise


Bytes 3 and 4 equate to the year (1562 to 2382)


Bytes 5 and 6 equate to the month of the year (01 to 12)


Bytes 7 and 8 equate to the day of the month (01 to 31)


Bytes 9-12 comprise an integer indicating the time of day starting at a value
of -3600000 for midnight i.e 00:00:00


The formula for calculating the time is:-


-3600000+(hours*3600*1000) +(minutes*60)1000) +(seconds*1000)


e.g at time of 23:59:59 would result in bytes 9-12 equating to a value of
-3600000+(23*3600*1000)+(59*60*1000)+(59*60) = 82743540






In order to understand recursion, you must first understand recursion.

 

[SJSFoxPro]

Many, many thanks for breaking out the 12 bytes for me!!!

I can now easily calculate the year, month, and day and it's working great. Times are all zero in my file, so I can't really verify anything there. My first 2 bytes apparently have newer values? I am getting 7424, which I haven't been able to interpret. But, I know they are sending me year, month, day, and zero time. Since I only need the date from the column and can translate it now, it's in good shape.

Thank you for such a quick response! Good to be able to move along so quickly!

 

[KenCunningham]

Have an extra star from me too, Taupiro - it's not every day things are hit bang on the nail like that!

I want to be good, is that not enough?