asked on

TIMESTAMP TIMESTAMP and TIMESTAMP......

This is a peculiar thing that i found with the oracle timestamp

When i do

Case1 :

select * from table
to_char(COLUMN1,'RRRR-MM-DD HH12:MI:SS AM')
>=
to_char(to_date('3/30/2006 1:20:20 AM','MM/DD/RRRR HH12:MI:SS AM'),'RRRR-MM-DD HH12:MI:SS AM')

it brings that records that satisfy the criteria ie COLUMN1 > 3/30/2006 1:20:20 AM

Case 2:

select * from table
to_char(COLUMN1,'RRRR-MM-DD HH12:MI:SS AM')
>=
to_char(to_date('3/30/2006 1:20:20 PM','MM/DD/RRRR HH12:MI:SS AM'),'RRRR-MM-DD HH12:MI:SS AM')

it brings that records that satisfy the criteria ie COLUMN1 > 3/30/2006 1:20:20 PM

BUT when i do alter the time to be 1:00:00 PM instead of 1:20:20 PM

select * from table
to_char(COLUMN1,'RRRR-MM-DD HH12:MI:SS AM')
>=
to_char(to_date('3/30/2006 1:00:00 PM','MM/DD/RRRR HH12:MI:SS AM'),'RRRR-MM-DD HH12:MI:SS AM')

it brings back stuff that it brought back for CASE 1 ie COLUMN1 > 3/30/2006 1:00:00 AM
instead of COLUMN1 > 3/30/2006 1:00:00 PM

I am not a geek but i am OK with oracle. I had a fix for that. But i am curious to know why this query is behaving this way....
Any help would be appreciated.

ram_0218

first of all, to do a date or timestamp comparison, you should not use to_char. Because to_char does an alphabetic sort not a date sort.

You gotto either compare with a to_date or with a timestamp.

ASKER CERTIFIED SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Harish_Rajani

Have a look,
Col1 is defined as Varchar2

SQL> select col1 from test_timestamp order by col1 desc;

COL1
------------------------------
3/30/2006 1:20:20 PM
3/30/2006 1:20:20 AM
3/30/2006 1:00:00 PM
3/30/2006 1:00:00 AM

1 select to_date(col1, 'MM/DD/RRRR HH12:MI:SS AM') Date_Col1, col1 from test_timestamp
2* order by Date_Col1 desc
SQL> /

Date_Col1 COL1
--------- ------------------------------
30-MAR-06 3/30/2006 1:20:20 PM
30-MAR-06 3/30/2006 1:00:00 PM
30-MAR-06 3/30/2006 1:20:20 AM
30-MAR-06 3/30/2006 1:00:00 AM

I hope you got the idea. Doing a character comparision where Date comparisions are required is asking for trouble. Might be actually useful to convert Chars to Date while doing comparision.

Rgds,
HR