| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Date Conversion
--0__=YwenYsaU8CiDPn9gGpJC5aXN7UKR2h9QrY8ltdx6ajbpgTUyx7lfl08n
Content-type: text/plain; charset=us-ascii
What you see is the number of miliseconds since January 1, 1970 - this is how the dates are stored internally in Java.
All you have to is write small PL/SQL procedure that would take this number
and divide it by 1000 - this would give you the number of seconds since
01/01/1970.
Then you have to divide it by 60 -- number of minutes,
then again by 60 -- hours and
then again by 24 - number of days since 1970.
Not it's a matter of simple math.
Depending on how precise you want to get when storing the date in Oracle database you can choose to either store it up until seconds or in truncated format (don't care about the date, then always use midnight).
Depending on the size of the table, it would be much easier to actually write a 10 line Java program that would do the conversion and update these dates into Oracle database and place them into the regular DATE column vs. PL/SQL code. The reason being is that Java actually will allow you to create a date based on this value that then can be easily stored into the database in the DATE format.
I am not sure why these guys decided to store these dates in Java INTERNAL format -- very inconvenient.
Tell them about java.sql.Date class and that it should be used whenever dealing with database DATE fields.
Val Gamerman
Oracle DBA
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc: (bcc: Val Gamerman/Victoria Financial) From: Jeff Wiegand <jwiegand_at_ancept.com> @ NOTES NET Date: 08/31/2000 04:17:49 PM GMT
Hello List.
We need to convert two columns in a table to date type. Currently, the two
columns are Varchar2. This wouldn't be a problem if there were actual dates
held in the columns. A Java application inserts into the table, and
inserted
in a format such as this:
964473249811
964473412927
964473649881
964473659746
964473882543
964473915782
964473972845
966970660171
Each one of those values represents a date. Is there a way to make sense of
this? A way to convert this to a date value Oracle will recognize. One
developer told me these are long date values (milliseconds since some date
or something).
--0__=YwenYsaU8CiDPn9gGpJC5aXN7UKR2h9QrY8ltdx6ajbpgTUyx7lfl08n
Content-type: text/html;
name="att1.htm"
Content-transfer-encoding: base64
Content-Description: Internet HTML
PCFET0NUWVBFIEhUTUwgUFVCTElDICItLy9XM0MvL0RURCBIVE1MIDMuMi8vRU4iPg0KPEhUTUw+ DQo8SEVBRD4NCjxNRVRBIEhUVFAtRVFVSVY9IkNvbnRlbnQtVHlwZSIgQ09OVEVOVD0idGV4dC9o dG1sOyBjaGFyc2V0PWlzby04ODU5LTEiPg0KPE1FVEEgTkFNRT0iR2VuZXJhdG9yIiBDT05URU5U PSJNUyBFeGNoYW5nZSBTZXJ2ZXIgdmVyc2lvbiA1LjUuMjY1MC4xMiI+DQo8VElUTEU+RGF0ZSBD b252ZXJzaW9uPC9USVRMRT4NCjwvSEVBRD4NCjxCT0RZPg0KDQo8UD48Rk9OVCBTSVpFPTI+SGVs bG8gTGlzdC48L0ZPTlQ+DQo8L1A+DQoNCjxQPjxGT05UIFNJWkU9Mj5XZSBuZWVkIHRvIGNvbnZl cnQgdHdvIGNvbHVtbnMgaW4gYSB0YWJsZSB0byBkYXRlIHR5cGUuIEN1cnJlbnRseSwgdGhlIHR3 byBjb2x1bW5zIGFyZSBWYXJjaGFyMi4gVGhpcyB3b3VsZG4ndCBiZSBhIHByb2JsZW0gaWYgdGhl cmUgd2VyZSBhY3R1YWwgZGF0ZXMgaGVsZCBpbiB0aGUgY29sdW1ucy4gQSBKYXZhIGFwcGxpY2F0 aW9uIGluc2VydHMgaW50byB0aGUgdGFibGUsIGFuZCBpbnNlcnRlZCBpbiBhIGZvcm1hdCBzdWNo IGFzIHRoaXM6PC9GT05UPjwvUD4NCg0KPFA+PEZPTlQgU0laRT0yPjk2NDQ3MzI0OTgxMTwvRk9O
VD4NCjxCUj48Rk9OVCBTSVpFPTI+OTY0NDczNDEyOTI3PC9GT05UPg0KPEJSPjxGT05UIFNJWkU9 Mj45NjQ0NzM2NDk4ODE8L0ZPTlQ+DQo8QlI+PEZPTlQgU0laRT0yPjk2NDQ3MzY1OTc0NjwvRk9O VD4NCjxCUj48Rk9OVCBTSVpFPTI+OTY0NDczODgyNTQzPC9GT05UPg0KPEJSPjxGT05UIFNJWkU9 Mj45NjQ0NzM5MTU3ODI8L0ZPTlQ+DQo8QlI+PEZPTlQgU0laRT0yPjk2NDQ3Mzk3Mjg0NTwvRk9O VD4NCjxCUj48Rk9OVCBTSVpFPTI+OTY2OTcwNjYwMTcxPC9GT05UPg0KPC9QPg0KDQo8UD48Rk9OVCBTSVpFPTI+RWFjaCBvbmUgb2YgdGhvc2UgdmFsdWVzIHJlcHJlc2VudHMgYSBkYXRlLiBJcyB0 aGVyZSBhIHdheSB0byBtYWtlIHNlbnNlIG9mIHRoaXM/IEEgd2F5IHRvIGNvbnZlcnQgdGhpcyB0 byBhIGRhdGUgdmFsdWUgT3JhY2xlIHdpbGwgcmVjb2duaXplLiBPbmUgZGV2ZWxvcGVyIHRvbGQg bWUgdGhlc2UgYXJlIGxvbmcgZGF0ZSB2YWx1ZXMgKG1pbGxpc2Vjb25kcyBzaW5jZSBzb21lIGRh dGUgb3Igc29tZXRoaW5nKS48L0ZPTlQ+PC9QPg0KDQo8L0JPRFk+DQo8L0hUTUw+DQo= Received on Thu Aug 31 2000 - 11:23:10 CDT
 |
 |