Home » SQL & PL/SQL » SQL & PL/SQL » how to run a date a day before using sysdate
how to run a date a day before using sysdate [message #206821] Fri, 01 December 2006 12:03 Go to next message
jonathan184
Messages: 21
Registered: November 2006
Junior Member

how to run a date a day before using sysdate

What i am trying to do is run a query in a date range using a dynamic date.
so basically i want to run yesterdays date between 9:30pm and 10:30pm


This is what I tried


 select count(*) from site s 
 where (s.lastupdateddate) 
 between to_date >=sysdate-(3.5/24)) and <=(sysdate-(2.5/24));      


Please help
Re: how to run a date a day before using sysdate [message #206825 is a reply to message #206821] Fri, 01 December 2006 12:14 Go to previous messageGo to next message
Nirmala
Messages: 43
Registered: October 2004
Member
If i understand correctly i think this is what you want.

select count(*) 
  from site s 
 where (s.lastupdateddate)  between  
       (to_date(sysdate,'dd:mm:yyyy')-1)+21.5/24 
   and (to_date(sysdate,'dd:mm:yyyy')-1)+22.5/24;  
 

[Updated on: Fri, 01 December 2006 12:15]

Report message to a moderator

Re: how to run a date a day before using sysdate [message #206835 is a reply to message #206825] Fri, 01 December 2006 12:52 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
TO_DATE(SYSDATE) doesn't make much sense because SYSDATE already IS a date. Format you used returns (on my database) year 0006 instead of 2006.

I'd rather put it this way:
SELECT COUNT(*)
FROM site
WHERE lastupdatedate BETWEEN TRUNC(SYSDATE) - 1 + 21.5/24
                         AND TRUNC(SYSDATE) - 1 + 22.5/24;
Re: how to run a date a day before using sysdate [message #206836 is a reply to message #206835] Fri, 01 December 2006 12:59 Go to previous messageGo to next message
Nirmala
Messages: 43
Registered: October 2004
Member
Thanks for correcting me.
Re: how to run a date a day before using sysdate [message #206838 is a reply to message #206836] Fri, 01 December 2006 13:03 Go to previous message
jonathan184
Messages: 21
Registered: November 2006
Junior Member


Hi Nirmala thanks for your reply.

Hi littlefoot that did work
the
SELECT COUNT(*)
FROM site
WHERE lastupdatedate BETWEEN TRUNC(SYSDATE) - 1 + 21.5/24
                         AND TRUNC(SYSDATE) - 1 + 22.5/24;


You guys helped me out thank you very much I am new at oracle so I appreciate all of your help people.

Thanks again
Previous Topic: Help loading CLOB data using SQLLDR or Insert statement
Next Topic: [CURSORS] Strange cursor behavior
Goto Forum:
  


Current Time: Tue Dec 06 10:19:56 CST 2016

Total time taken to generate the page: 0.14495 seconds