OraFAQ Forum: SQL & PL/SQL » detect and fix the negative time in oracle

Home » SQL & PL/SQL » SQL & PL/SQL » detect and fix the negative time in oracle

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

detect and fix the negative time in oracle [message #516240]

Fri, 15 July 2011 12:20

casttree
Messages: 83
Registered: August 2008

Member

I found the data in DB with negative time as below

Quote:

1 SELECT
2 c.time
3 FROM partition c
4 WHERE
5* c.time < to_date('0001-01-01','YYYY-MM-DD')
SQL> /

DEACTIVATION_TIME
------------------------------------------------------------------------
17-JUN-08 04.06.22.893 PM

The data type for c.time is Timestamp.

When I use JDBC to retrieve the data and convert it to mill seconds, and it also shows as negative time.

My question is the retrieved time doesn't show the negative date, but obvious it is a BC date and earlier than 0001-01-01.

1. How can we detect/show the date to show correctly for the BC date without checking c.time < to_date('0001-01-01','YYYY-MM-DD')? (I am wondering if there is format configuation to show the time like 17-JUN-08 04.06.22.893 PM BC, or somthing to show the time is negative )

2.how could we fix it negative time to positive ?

Thanks

Thanks

Report message to a moderator

Re: detect and fix the negative time in oracle [message #516242 is a reply to message #516240]

Fri, 15 July 2011 12:24

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

How can we detect/show the date to show correctly for the BC date without checking c.time

Use TO_CHAR with the appropriate format mask.

Quote:

how could we fix it negative time to positive ?

Apply a correct rule for this.
Which (correct) date should be " 17-JUN-08 04.06.22.893 PM BC"?

Regards
Michel

Report message to a moderator

Re: detect and fix the negative time in oracle [message #516247 is a reply to message #516242]

Fri, 15 July 2011 13:06

casttree
Messages: 83
Registered: August 2008

Member

Thanks, Michel,

Now I can see the time as "2008-06-17 BC" with to_char. Could you give me more information what rule we can apply to prevent the negative date like this to be inserted to oracle Database?

Thanks

Report message to a moderator