• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1484
  • Last Modified:

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.
0
crystalguy2000
Asked:
crystalguy2000
1 Solution
 
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
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now