Home » RDBMS Server » Server Administration » Wrong dates in some records in Timstamp with local zone in 10g (10g)
Wrong dates in some records in Timstamp with local zone in 10g [message #661608] |
Fri, 24 March 2017 13:49 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/85f20b955cfa8ffedf947ebccc18a461?s=64&d=mm&r=g) |
OraclePE
Messages: 10 Registered: March 2017
|
Junior Member |
|
|
I have a very strange problem in oracle 10g where I have a table that contain column in time stamp with time zone which is giving wrong dates like 2048-xx-xxxx 00:00:34 or some records with year 0001 etc. but 90% of records are in proper date time stamp. what could be the reason for this? Please help.
|
|
|
|
|
Re: Wrong dates in some records in Timstamp with local zone in 10g [message #661617 is a reply to message #661611] |
Sat, 25 March 2017 10:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/85f20b955cfa8ffedf947ebccc18a461?s=64&d=mm&r=g) |
OraclePE
Messages: 10 Registered: March 2017
|
Junior Member |
|
|
select * from v$version where rownum=1;
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
---------------------------------------
desc lbt_archive_170320_exp
Name Null Type
------------------ ---- ---------------------------------
EVENTTIME TIMESTAMP(6) WITH LOCAL TIME ZONE
BAGTAGID VARCHAR2(12)
BAG_ID NUMBER(10)
STATUS VARCHAR2(3)
BAGSTATUS VARCHAR2(20)
BAGPROFILE VARCHAR2(3)
HBSSTATUS VARCHAR2(3)
HBSCURLEVEL VARCHAR2(3)
HBSRESULT VARCHAR2(10)
CUSTSTATUS VARCHAR2(10)
BSM_ID VARCHAR2(20)
TTR_ID NUMBER(10)
TDP_IDFINAL NUMBER(6)
TDP_IDCURR NUMBER(6)
TDP_IDOPER NUMBER(6)
TNP_IDFIRSTPOS NUMBER(6)
TNP_IDLASTPOS NUMBER(6)
FIRSTPOSTIME TIMESTAMP(0) WITH LOCAL TIME ZONE
LASTPOSTIME TIMESTAMP(0) WITH LOCAL TIME ZONE
EXPIRATIONTIME TIMESTAMP(0) WITH LOCAL TIME ZONE
SECTODEST NUMBER(6)
REASON VARCHAR2(20)
STORINGREASON VARCHAR2(20)
ISNPBASEDHBSSTATUS VARCHAR2(1)
ISPROCESSED VARCHAR2(1)
-------------------------------------------------------------------------
select to_char(eventtime,'DD/MM/YYYY HH24:MI:SS.FF6 TZR') dt, dump(eventtime) dmp
from lbt_archive_170320_exp
where eventtime > sysdate;
DT DMP
00/00/2047 16:50:57.251857 ASIA/MUSCAT Typ=231 Len=11: 120,147,0,0,13,171,58,15,3,7,225
00/00/2047 16:50:57.251857 ASIA/MUSCAT Typ=231 Len=11: 120,147,0,0,13,171,58,15,3,7,225
00/00/2047 16:50:57.251857 ASIA/MUSCAT Typ=231 Len=11: 120,147,0,0,13,171,58,15,3,7,225
03/01/2047 08:51:52.251857 ASIA/MUSCAT Typ=231 Len=11: 120,147,0,0,77,172,53,15,3,7,225
03/01/2047 08:51:52.251857 ASIA/MUSCAT Typ=231 Len=11: 120,147,0,0,77,172,53,15,3,7,225
03/01/2047 08:51:52.251857 ASIA/MUSCAT Typ=231 Len=11: 120,147,0,0,77,172,53,15,3,7,225
please find the output from script.
*BlackSwan added {code} tags. PLEASE do so yourself in the future. You have been TWICE how to do so!
[Updated on: Sat, 25 March 2017 10:14] by Moderator Report message to a moderator
|
|
|
|
|
Re: Wrong dates in some records in Timstamp with local zone in 10g [message #661620 is a reply to message #661617] |
Sat, 25 March 2017 10:38 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
OraclePE wrote on Sat, 25 March 2017 16:11...
select to_char(eventtime,'DD/MM/YYYY HH24:MI:SS.FF6 TZR') dt, dump(eventtime) dmp
from lbt_archive_170320_exp
where eventtime > sysdate;
DT DMP
00/00/2047 16:50:57.251857 ASIA/MUSCAT Typ=231 Len=11: 120,147,0,0,13,171,58,15,3,7,225
00/00/2047 16:50:57.251857 ASIA/MUSCAT Typ=231 Len=11: 120,147,0,0,13,171,58,15,3,7,225
00/00/2047 16:50:57.251857 ASIA/MUSCAT Typ=231 Len=11: 120,147,0,0,13,171,58,15,3,7,225
03/01/2047 08:51:52.251857 ASIA/MUSCAT Typ=231 Len=11: 120,147,0,0,77,172,53,15,3,7,225
03/01/2047 08:51:52.251857 ASIA/MUSCAT Typ=231 Len=11: 120,147,0,0,77,172,53,15,3,7,225
03/01/2047 08:51:52.251857 ASIA/MUSCAT Typ=231 Len=11: 120,147,0,0,77,172,53,15,3,7,225
These are indeed wrong values, and have been entered in the database by an application using binary values.
These can't be entered with usual SQL interface, ONLY with binary interface, for which the application is responsible of the correctness of the values.
Once again, read How to use [code] tags and make your code easier to read.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: Wrong dates in some records in Timstamp with local zone in 10g [message #661677 is a reply to message #661660] |
Mon, 27 March 2017 11:20 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.gravatar.com/avatar/d29f577f753cb4b873212fd5ed0da4cd?s=64&d=mm&r=g) |
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
I don't know if it part of your current problem, but looks like it could be.
The RR format was meant to be a temporary band-aid to buy time in dealing with Y2k. That was over 17 years ago. It's well past time to quit using it. It was never meant to be a permanent fixture. For those that don't understand its impact, and thought it was a permanent solution to y2k, to be set and then ignored forever, it wasn't so much a fix as a time-bomb.
|
|
|
Goto Forum:
Current Time: Sat Sep 07 21:27:37 CDT 2024
|