Home » SQL & PL/SQL » SQL & PL/SQL » adding number in sysdate (Oracle 11g )
adding number in sysdate [message #583167] Fri, 26 April 2013 08:17 Go to next message
ashwani0301
Messages: 44
Registered: March 2013
Location: Karnatka, Bangalore
Member
Hi All,

Can we add 10000 days in SYSDATE using + operator ?

Please provide me answer.

Quote:
This I can test it but I don't have database access now that's why I am asking this question. Please


Thanks

Re: adding number in sysdate [message #583170 is a reply to message #583167] Fri, 26 April 2013 08:45 Go to previous messageGo to next message
sss111ind
Messages: 473
Registered: April 2012
Location: India
Senior Member

SELECT SYSDATE+10000 FROM dual;

11-SEP-40 19:14:16
Re: adding number in sysdate [message #583171 is a reply to message #583170] Fri, 26 April 2013 08:45 Go to previous messageGo to next message
ashwani0301
Messages: 44
Registered: March 2013
Location: Karnatka, Bangalore
Member
Thank you very much.
Re: adding number in sysdate [message #583172 is a reply to message #583167] Fri, 26 April 2013 08:47 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1999
Registered: January 2010
Senior Member
Oracle date arithmetic uses day as unit of measure. So just write sysdate + 10000:

SQL> alter session set nls_date_format='mm/dd/yyyy hh24:mi:ss'
  2  /

Session altered.

SQL> select  sysdate,
  2          sysdate + 10000 sysdate_plus_10000
  3    from  dual
  4  /

SYSDATE             SYSDATE_PLUS_10000
------------------- -------------------
04/26/2013 09:46:29 09/11/2040 09:46:29

SQL> 


SY.
Re: adding number in sysdate [message #583174 is a reply to message #583172] Fri, 26 April 2013 08:57 Go to previous messageGo to next message
sss111ind
Messages: 473
Registered: April 2012
Location: India
Senior Member

Why there IS A limitation OF adding NUMBER TO SYSDATE, AS follows.
SELECT To_char(To_date('31-DEC-9999', 'DD-MM-RRRR'), 'J') ORACLE_LAST_JULIAN_NUMBER,
       To_char(SYSDATE, 'J')  TODAYS_JULIAN_NUMBER,
       To_number(To_char(To_date('31-DEC-9999', 'DD-MM-RRRR'), 'J')) -To_number(To_char(SYSDATE, 'J'))JULIAN_NUMBER_LEFT
FROM   dual;  

Otherwise getting error
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
01841. 00000 -  "(full) year must be between -4713 and +9999, and not be 0"
*Cause:    Illegal year entered
*Action:   Input year in the specified range

[Updated on: Fri, 26 April 2013 08:58]

Report message to a moderator

Re: adding number in sysdate [message #583175 is a reply to message #583174] Fri, 26 April 2013 09:05 Go to previous message
Michel Cadot
Messages: 58905
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Why there IS A limitation OF adding NUMBER TO SYSDATE


Why? See the possible format mask and you will know the reason.

Regards
Michel
Previous Topic: function returning pl/sql table of dynamic structure
Next Topic: how to debug package
Goto Forum:
  


Current Time: Wed Aug 27 09:56:58 CDT 2014

Total time taken to generate the page: 0.06925 seconds