CODEWITHSUNDEEP
oracle-databasedate-arithmetic
user1024858
user1024858

Reputation: 129

Compare two times in oracle

In Oracle :

My table has a timestamp column with time value of '02-NOV-11 12.00.00.000000000 AM' .

I tried to compare two dates:

select id from table1 where mytime = '02-NOV-11'

But it returns no records. Why?

Thanks!!!

Upvotes: 2

Views: 733

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132570

I suspect it may be the default date format that is causing the problem, since the query works fine for me. It is not good practice to write queries that compare dates with strings, since this requires Oracle to format the string into a date using the default format, which may not be as you expect. It is better to use ANSI date literals like this:

select id from table1 where mytime = date '2011-11-02';

ANSI date literals must be in the format date 'yyyy-mm-dd', so there is no ambiguity about format.

A reference for datetime literals

Upvotes: 6

Related Questions