Date Selection 1

[kskid]

Can someone explain why the following works

SELECT *
FROM TABLEA
WHERE TABLEA.DATE = TO_DATE('20060605','YYYYMMDD')

but not this

SELECT *
FROM TABLEA
WHERE TABLEA.DATE = SYSDATE - 1

I'm using Oracle 8i

 

[SantaMufasa]

...but not this...

I cannot clearly see your terminal in Kansas, so please help me out here...Is your problem a syntax error, a run-time error, or a logic-error (the code displays unexpected/unwanted results)?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[SantaMufasa]

...and please be sure than when you answer my inquiry that you post the actual outcome that represents your "...but not this..." results.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[BJCooperIT]

Oracle dates have a time component:

SQL> select to_char(SYSDATE,'YYYY MON DD HH24:MI:SS') NOW
  2  from DUAL
  3  /

NOW
--------------------
2006 JUN 06 14:26:47

SQL>

thus SYSDATE will not be equal to a stored date that has no time component.

Remove the time element by:

SELECT *
FROM TABLEA
WHERE TABLEA.DATE = trunc(SYSDATE) - 1

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[kskid]

That did it.

On the first query, I had 779 rows returned but none with the second. The date field is strictly a date and not datetime so the TRUNC(SYSDATE-1) solved it.

 

[SantaMufasa]

Barb,

Great intuition...great catch!

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[BJCooperIT]

Thanks. This has to be one of the most frequent questions I have been asked by developers. Now, whenever I see anything compared to SYSDATE, it raises a huge red flag!