Date Comparison giving false results

[jwalacha]

Hi,

I am getting incorrect results for my select statement when querying the table with the following information:

Timestamp ID
-------------------------------
08/03/02 03:06:18 100
08/03/02 03:06:59 101
08/03/02 03:16:57 226
08/04/02 12:12:12 227

The query that I am using is:

select ID from TABLE1
where TIMESTAMP > to_date('08/03/02 03:16:57','mm/dd/yy hh24:mi:ss');

I would expect to get the result of ID = 227
But it does not give me the expected result.

I appreciate your help.

Thanks,

Jwalant.

 

[BJCooperIT]

Try running this statement to double check your centuries:

select ID,
       to_char(TIMESTAMP,'mm/dd/yyyy hh24:mi:ss')
       full_date_time
from TABLE1
order by TIMESTAMP;

Maybe this will help you see the problem.

select * from Life where Brain is not null

Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
[sup]When posting code, please use TGML for readability. Thanks![sup]

 

[jwalacha]

BJCooperIT,

I ran your code and the value of year was 2002, which meant that I was in the correct century.

Although I change my select statement from 'yy' to 'yyyy' format to eliminate any doubts and the query result was correct.
Thank you for your response.

I'd appreciate if you can answer my next question:

Please refer to the table contents in the above email.

The following query...

select ID from TABLE1
where TIMESTAMP > to_date('08/03/02 03:06:59','mm/dd/yy hh24:mi:ss');

would give me two (2) records, ID = 226 and 227 respectively.

Is there a quick way to write a select statement so that I only get the record that has the ID for the timestamp right after 8/3/02 03:06:59, namely ID 226 ?

I hope I was clear in stating my question.

Thanks,

Jwalant.

 

[BJCooperIT]

Now that I understand your question a bit better...
I cannot get an SQL statement to do this on my operating system. My solution would be to create a function that would contain a cursor that would read the first record dated after your desired date and return its rowid. Your SQL would look something like:

select ID from TABLE1
where  rowid = F_GET_TABLE1_ROWID_AFTER_DATE
  (to_date('20020803030659','YYYYMMDDHH24MISS'));

Perhaps someone else can offer an SQL statement using an inline view with rownum that will work on your system.

Good Luck!

select * from Life where Brain is not null

Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
[sup]When posting code, please use TGML for readability. Thanks![sup]

 

[jwalacha]

Thanks for your response!

 

[ManjulaMadhu]

try this:

select *
from (
select a.*,
row_number() over ( order by timestamp desc)
rn
from table1 a
where TIMESTAMP >to_date('08/03/02 03:06:59','mm/dd/yy hh24:mi:ss'))
where rn=1

 

[sem]

If you work with recent dates, you may use 'RR' mask instead of 'YY'. This will treat '02' as '2002' and '50' as '1950'.

Regards, Dima

 

[jwalacha]

Thank you Manjula,
That was exactly what I was looking for!

I appreciate everybody's input in helping me with a solution.

Regards,

Jwalant.

 

[Turkbear]

Hi, Just a nitpick..Why use RR ?
Typing 2 more chars ( YYYY ) will insure century correctness..( Until the Y10K problem surfaces)
Just my 2c

 

[sem]

Turkbear: can you explain the same to our customers?

Regards, Dima