Home » SQL & PL/SQL » SQL & PL/SQL » add two dates
add two dates [message #259062] Tue, 14 August 2007 05:53 Go to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
How should i add two dates??


SELECT TO_DATE(TO_CHAR(SYSDATE,'YY-MM-DD HH24:MI:SS'),'YY-MM-DD HH24:MI:SS') +TO_DATE('01-05-01 04:12:27','YY-MM-DD HH24:MI:SS') FROM DUAL

This query gives an error. I want to add two dates and display the result
Re: add two dates [message #259070 is a reply to message #259062] Tue, 14 August 2007 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Tell me what is the meaning of addind 2 dates? What is the meaning of your query?
Or if you prefer, what are you trying to do? What is the business need you try to achieve?

Regards
Michel
Re: add two dates [message #259084 is a reply to message #259062] Tue, 14 August 2007 06:30 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
In addition to Michel's post:
No formatting
No version
You mention that you get an error but don't post what that error is.
Re: add two dates [message #259094 is a reply to message #259084] Tue, 14 August 2007 06:45 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, the result is somewhere around the end of the world.
SELECT TO_DATE(TO_NUMBER(TO_CHAR(SYSDATE, 'j')) +
               TO_NUMBER(TO_CHAR(TO_DATE('01-05-01 04:12:27','YY-MM-DD HH24:MI:SS'), 'j'))
               , 'j') result
FROM dual;

RESULT
----------
19.01.8721

In other words, to add two dates, convert them to numbers (Julian dates) and back.
Re: add two dates [message #259113 is a reply to message #259070] Tue, 14 August 2007 07:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm with Michael on this one - I don't believe that
23rd June 1847 + 15th December 1998
has any meaning at all.
You can add a period of time onto a date - in Oracle you do that by adding a (potentially fractional) number of days onto a date, eg
sysdate + 2.5
but adding dates to other dates is not possible.
Re: add two dates [message #259145 is a reply to message #259113] Tue, 14 August 2007 09:14 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or you can use the well named INTERVAL datatype:
SQL> select sysdate, sysdate + interval '2 6' day to hour from dual;
SYSDATE             SYSDATE+INTERVAL'26
------------------- -------------------
14/08/2007 16:13:49 16/08/2007 22:13:49

1 row selected.

Regards
Michel
Previous Topic: How to write a single query that delimits the string by count of 3?
Next Topic: Substr Query Problem
Goto Forum:
  


Current Time: Sun Dec 11 08:28:35 CST 2016

Total time taken to generate the page: 0.19052 seconds