Home » SQL & PL/SQL » SQL & PL/SQL » Age related problem (merged)
Age related problem (merged) [message #201178] Thu, 02 November 2006 21:19 Go to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
hello all

i want to get result as what i m inserting value of date will execute adding 5months & 2days to my data.

suppose my input is

03-nov-2006

output will be -- 05-apr-2007.

i did a query which is correct only for sysdate.
not for other value which i m inserting, if i m using '&' before sysdate it won't work.

select to_char(sysdate+2, 'dd')||'-'||to_char(add_months(sysdate, 5), 'mon -yyyy') from dual;

TO_CHAR(SYSD
------------
05-apr -2007

please provide me your kind help.

with regards

ashish

[Updated on: Thu, 02 November 2006 21:33]

Report message to a moderator

problem on age related [message #201180 is a reply to message #201178] Thu, 02 November 2006 21:31 Go to previous messageGo to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
hello orafaq team

i want to know that why my query doesn't work on other's value?
please provide me help & convey me about my mistake.

i want to do a query which accept any data but display the date & day after 3 days.


my query is
SQL>select to_char(to_date(next_day('&date', 'monday'), 'dd-mon-yyyy') from dual;

this works only for
input is-- 03-nov-2006

output is--- 06-nov-2006

but for other's it fail.

please provide me some hints.

with regards
ashish

[Updated on: Thu, 02 November 2006 21:32]

Report message to a moderator

Re: age related problem [message #201191 is a reply to message #201178] Thu, 02 November 2006 23:21 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

you can try this.

select curr_date,
        add_months(curr_date,5)+2 future_date 
            from (
                  select to_date('01-11-2006','dd-mm-yyyy') curr_date from dual
                 )



regards,
Re: problem on age related [message #201205 is a reply to message #201180] Fri, 03 November 2006 01:10 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I'm afraid.

It is doing what it intend to do.

Please have a look at the documentation about next_day.

If you want to get the date and day after 3 days, you just need to add 3 to the date and use required format in to_char.

By
Vamsi
Re: problem on age related [message #201207 is a reply to message #201180] Fri, 03 November 2006 01:26 Go to previous messageGo to next message
romi
Messages: 67
Registered: October 2006
Member

U can use it


sql>select to_char(to_date('&date','dd-mon-yyyy')+3) from dual;
Re: Age related problem (merged) [message #201321 is a reply to message #201178] Fri, 03 November 2006 08:50 Go to previous message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

Romi said correct but small modification

select to_date('&date','dd-mon-yyyy') + 3 from dual;

if u want to display in any format such as dd-mm-yyyy then

select to_char(to_date('&date','dd-mon-yyyy') + 3,'DD-MM-YYYY') from dual;


Previous Topic: EXCEPTION Handling Stored Procedure
Next Topic: Help required in DYNAMIC SQL coding (Merged)
Goto Forum:
  


Current Time: Fri Dec 09 23:33:24 CST 2016

Total time taken to generate the page: 0.05381 seconds