SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

how to use date function in my procedure

User430585 -OracleAug 10 2012 — edited Aug 16 2012

hi Gurus,

i have a requirement

where user passes the end_date parametre from front end which get convered to

End Date => 2012/05/31 00:00:00

now i based on this end date value i have to calculate the start date which is only month less than the end date, i know i can use add_months function for this requirement but
my problem is

l_start_date :=add_months(fnd_date.canonical_to_date(p_end_date),-1);

(OR)

l_start_date := add_months(to_date(end_date,'DD-MON-YYYY'),-1);

all these are returning date format as DD-MON-YYYY eg;
if user passes from front end 31-may-2012 , in the backend to my procedure it is passing as 2012/05/31 00:00:00
and my procedure calculating start date and returning as '01-MAY-12',
but my requiremnt is
it should return
Start Date => 2012/05/01 00:00:00

any pointers on this is highly appreciable,

thanks in advancee