Date comparison

[engineer2100]

I would like a small query that would check a Date value to be between yesterday 1600hrs to today 1600hrs.

Thanks in Advance

-Engi

 

[sem]

...between trunc(sysdate) - 1/3 and trunc(sysdate) + 2/3

Regards, Dima

http://seminihin.narod.ru

 

[engineer2100]

thank you SEM. is it possible for you to explain me the 1/3 and 2/3 concept!!

thanks
-Engi

 

[SantaMufasa]

(Since it is nearly 7:00 p.m. in Sem's part of the world (Kiev, Ukraine), and he may have gone home to his lovey family, I'll contribute here in his behalf.)

Given Sem's code:

...between trunc(sysdate) - 1/3 and trunc(sysdate) + 2/3

...it means, "
1) start with the current date/time ("sysdate")
2) cut of the time component ("trunc(sysdate)"), yielding midnight ("00:00") at the beginning of today.
3) subtract 1/3 of day ("8 hours") from midnight, yielding "midnight minus 8 hours", yielding "1600hrs yesterday".
4) Do steps 1 & 2, above, but add 2/3 of a day ("16 hours") to midnight this morning, yielding "1600hrs today".

Very clever, that Sem/Dima, huh.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[SantaMufasa]

Although Sem's family is probably a "lovey" family, I meant to say "lovely family"...Bad, chubby fingers, bad !

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.