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.
crystalguy2000Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ram_0218Commented:
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.
0
schwertnerCommented:
I think this will not happen if you use 'Military time' --->  HH24 format for Hours.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Harish_RajaniCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.