Home » SQL & PL/SQL » SQL & PL/SQL » Adding time to a date parameter
Adding time to a date parameter [message #359366] Sat, 15 November 2008 05:10 Go to next message
chuck1000
Messages: 5
Registered: July 2008
Junior Member
I want to add time to an entered date parameter in my sqlplus script. e.g. if 14/11/2008 is entered I want it to be replaced by 14/11/2008 20:00:00. see below

and ds.decision_date BETWEEN
TO_DATE('14/11/2008 20:00:00', 'DD/MM/YYYY HH24:MI:SS')
AND TO_DATE('15/11/2008 23:59:59', 'DD/MM/YYYY HH24:MI:SS')

any ideas?
Re: Adding time to a date parameter [message #359368 is a reply to message #359366] Sat, 15 November 2008 05:50 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Dates are represented internally as numbers where "1" equals 1 day.

Dates without any time component are set by default to midnight at the beginning of the day.

So to set the time on a date to exactly 20:00:00 (8pm), you just add the fraction of a day (20/24) like it was a number.

Ross Leishman
Re: Adding time to a date parameter [message #359370 is a reply to message #359368] Sat, 15 November 2008 06:49 Go to previous messageGo to next message
chuck1000
Messages: 5
Registered: July 2008
Junior Member
yeah, tried that one but my syntax must have been wrong.

Got it working now, my code looking like this:-

and ds.decision_date between
to_date('&&date1 + 20/24', 'DD/MM/YYYY HH24:MI:SS')
and TO_DATE('&&date2', 'DD/MM/YYYY HH24:MI:SS').

I want to be able to take this into an Oracle report but when I replace it with:-

and ds.decision_date between
to_date(:pi_start_date + 20/24, 'DD/MM/YYYY HH24:MI:SS')
and TO_DATE(:pi_end_date, 'DD/MM/YYYY HH24:MI:SS')

I get an invalid number error message when running. This may not be the right forum for this but worth a try.

cheers
Re: Adding time to a date parameter [message #359377 is a reply to message #359370] Sat, 15 November 2008 09:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You first translate your date string to a date datatype and then you add 20/24.

Regards
Michel
Re: Adding time to a date parameter [message #359564 is a reply to message #359366] Mon, 17 November 2008 05:29 Go to previous messageGo to next message
chuck1000
Messages: 5
Registered: July 2008
Junior Member
Thanks very much to both of you.
Re: Adding time to a date parameter [message #359571 is a reply to message #359564] Mon, 17 November 2008 06:11 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You an use the INTERVAL syntax as well:
select trunc(sysdate) + interval '20' hour 
from dual;
Previous Topic: Need help in merging two database
Next Topic: Want to check how bussy the oracle server is.
Goto Forum:
  


Current Time: Sun Dec 11 00:31:41 CST 2016

Total time taken to generate the page: 0.20233 seconds