Oracle 11g - Sql with TimeStamp

Question

I have a table named Evrak and it has two columns which are Product_Date [timestamp(6)] and Evrak_Name[varchar2(50)]. There is a record which name is Evrak Test and Product_Date is
25-APR-12 12.00.00.000000000 PM.

When i want to run a query like ;

select * from Evrak Where Evrak.Product_Date  = TO_TIMESTAMP ('25.04.2012:00:00:00','DD.MM.YYYY:HH24:MI:SS')

It does return a null record, i' ve tried to change format type and have used to_date, to_char methods but did not managed to get the record. What am i missing here ? or is there any special usage while quering a timestamp column?

A.B.Cade · Accepted Answer · 2012-05-09 21:02:51Z

2

Try:

select * 
 from Evrak 
Where Evrak.Product_Date  = TO_TIMESTAMP ('25.04.2012:12:00:00','DD.MM.YYYY:HH24:MI:SS')

Since you want 25-APR-12 12.00.00.000000000 PM and not 25-APR-12 12.00.00.000000000 AM

answered May 9, 2012 at 21:02

A.B.Cade

16.9k1 gold badge39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2012-05-09 15:31:55Z

0

Time stamps are finicky. Like floating point numbers, there may be insignificant bits that affect comparisons.

I would suggest returning things within a range. For instance, the following query returns time stamps within one second.

select *
from Evrak
Where abs(Evrak.Product_Date - TO_TIMESTAMP ('25.04.2012:00:00:00', 'DD.MM.YYYY:HH24:MI:SS')) < 1.0/(24*60*60)

answered May 9, 2012 at 15:31

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

Burhan Ali Over a year ago

Range or precision wasn't the issue. It was asking for the wrong time. See A B Cade's answer.

Collectives™ on Stack Overflow

Oracle 11g - Sql with TimeStamp

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related