Sysdate Question

[Hackster]

Why are there no rows selected when clearly there is a class with a start date that equals sysdate + 3?

SQL> select sysdate + 3 from dual;

SYSDATE+3
---------
18-OCT-04

SQL> select class_no from tpt_classes where start_date = sysdate + 3;

no rows selected

SQL> select start_date from tpt_classes where class_no = '047483';

START_DAT
---------
18-OCT-04

SQL>

 

[jdemmi]

Could it be a time issue?

Just to double check, try...

select class_no from tpt_classes where start_date >= sysdate + 3;



-- Jason
"It's Just Ones and Zeros

 

[jdemmi]

Or you could also try....

select class_no from tpt_classes where start_date LIKE sysdate + 3;

-- Jason
"It's Just Ones and Zeros

 

[Hackster]

jdemmi: This one was not too bad except that it returned 26 rows instead of 2 (there are only two classes in the table that have a start date of October 18 (which is sysdate + 3 from today))

Also, I thought of the >= approach except that there will always be classes that start greater then 3 days from now and I must ONLY deal with classes that start exactly 3 days from now (I'm writting a stored procedure that will be run as a batch job at night, and the procedure must effect a change on all classes that start three days from the run date and ONLY classes that start three days from the run date.

I just don't understand why Oracle, on the one hand knows what day is three days from now, but doesn't seem to be able to match that date with a date in a date/time field in my table.

 

[Hackster]

Got it!!! In case anyone is interested in the solution, here it is:

select class_no from tpt_classes where start_date > sysdate
and start_date < (select sysdate +3 from dual)

 

[SantaMufasa]

Hackster,

You may have produced results you wanted, but if what you wanted is classes that begin at any time of the day three days from now, then the pure solution to your issues (with changes in bold is:

select class_no from tpt_classes where [b]trunc([/b]start_date[b])[/b] = [b]trunc([/b]sysdate[b])[/b] + 3;

This is because all Oracle dates contain not only date components, but also time components down to the second. The only way that a class would match your original criteria is if its start date and time match the exact time in three days from now. That is why we truncate both dates (which default truncates to just the date component).

Does this now make everything clear?

Mufasa
(aka Dave of Sandy, Utah, USA @ 15:55 (15Oct04) UTC (aka "GMT" and "Zulu"), 08:55 (15Oct04) Mountain Time)

 

[Hackster]

You da man Mufasa!!!!!!!!

Thanks!!!

 

[sem]

If you also plan to utilize an index on start_date you may use

select class_no from tpt_classes where start_date >= trunc(sysdate) + 3
and start_date < trunc(sysdate) + 4;
[code]

It looks more complex but works faster if your table is quite big and start_date is indexed.

Regards, Dima
[URL unfurl="true"]http://seminihin.narod.ru[/URL]