Home » SQL & PL/SQL » SQL & PL/SQL » Previous Date and Time Calculation
Previous Date and Time Calculation [message #285917] Thu, 06 December 2007 01:41 Go to next message
muthaharora
Messages: 11
Registered: August 2007
Junior Member
Hi All,

We want to know how will be the select statement for the below condition
For one of our Calculation:

SYSDATE will be the input and we have to calculate 3 days prior to sysdate, it can be done using (select sysdate-3 from dual), but we want sysdate to return the time morning 8.00 a.m of the prior date.

eg: sysdate: 2007-DEC-06 02:25 AM , as per the query it retrives 2007-DEC-03 02:25 AM

We want always the date to be started previous date to be started with 08:00 AM.

Could you please help in finding a solution.

Thanks and Regards
Muthahar
Re: Previous Date and Time Calculation [message #285920 is a reply to message #285917] Thu, 06 December 2007 01:48 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
SELECT trunc(sysdate) - 3 + (8/24)
FROM dual
Re: Previous Date and Time Calculation [message #285925 is a reply to message #285920] Thu, 06 December 2007 02:02 Go to previous messageGo to next message
muthaharora
Messages: 11
Registered: August 2007
Junior Member
hi rleishman,

Thanks for your reply below is the query and output which i got while executing the statement,

SELECT to_CHAR(trunc(sysdate-3+ (8/24)),'YYYY-MON-DD HH24:MI AM'), to_CHAR(sysdate+1,'YYYY-MON-DD HH24:MI AM') FROM DUAL

i got the output as;

2007-DEC-03 00:00 AM 2007-DEC-07 03:09 AM

How could i get :2007-DEC-03 08:00 AM this is an example but i should always get 08:00 AM for all the previous date, by taking current sysdate as input.

please give your suggestions

Thanks and Regards
Muthahar
Re: Previous Date and Time Calculation [message #285926 is a reply to message #285925] Thu, 06 December 2007 02:04 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Take a very careful look at your parentheses. Compare that to what Ross posted
Re: Previous Date and Time Calculation [message #285932 is a reply to message #285917] Thu, 06 December 2007 02:16 Go to previous message
muthaharora
Messages: 11
Registered: August 2007
Junior Member
Hi Frank,

Thanks for pointing out the mistake, and sorry for that, now its working fine.

Thanks and Regards
Muthahar
Previous Topic: Triggers
Next Topic: function
Goto Forum:
  


Current Time: Sat Dec 10 07:12:45 CST 2016

Total time taken to generate the page: 0.09777 seconds