OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » add two dates

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

add two dates [message #259062]

Tue, 14 August 2007 05:53

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

Report message to a moderator

Re: add two dates [message #259070 is a reply to message #259062]

Tue, 14 August 2007 06:11

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: add two dates [message #259084 is a reply to message #259062]

Tue, 14 August 2007 06:30

pablolee
Messages: 2882
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.

Report message to a moderator

Re: add two dates [message #259094 is a reply to message #259084]

Tue, 14 August 2007 06:45

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: add two dates [message #259113 is a reply to message #259070]

Tue, 14 August 2007 07:44

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.

Report message to a moderator

Re: add two dates [message #259145 is a reply to message #259113]

Tue, 14 August 2007 09:14

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 07 06:37:16 CST 2024