OraFAQ Forum: Forms » Annual leave of the year

Home » Developer & Programmer » Forms » Annual leave of the year

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

Annual leave of the year [message #643594]

Tue, 13 October 2015 04:47

mehboob110233
Messages: 55
Registered: February 2014
Location: Pakistan

Member

Dear all,
i am working on leave annual system. now i face problem to get annual year of the employee like
if employee joining date is
24-jun-2004 every year his leave year start from 24-jun of the year now i enter leave date 13-oct-2015 it will how return me leave start from 24-jun-2015
please guide me.

Report message to a moderator

Re: Annual leave of the year [message #643595 is a reply to message #643594]

Tue, 13 October 2015 05:18

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Get employee start date.
Convert it to current year - extract the day and month with to_char, tack on current year and convert back to a date.
Compare calculated date to entered date, if it's later than entered date subtract a year from calculated date using add_months.

Report message to a moderator

Re: Annual leave of the year [message #643597 is a reply to message #643595]

Tue, 13 October 2015 05:38

mehboob110233
Messages: 55
Registered: February 2014
Location: Pakistan

Member

Sir Kindly guide which query return me 24-jun-2015 as "employee leave year start from"
If employee joining date is 24-jun-2004

Report message to a moderator

Re: Annual leave of the year [message #643598 is a reply to message #643597]

Tue, 13 October 2015 05:51

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

extract the day and month with to_char, tack on current year and convert back to a date.

Is this not help?

[Edit: add link]

[Updated on: Tue, 13 October 2015 05:52]

Report message to a moderator

Re: Annual leave of the year [message #643601 is a reply to message #643598]

Tue, 13 October 2015 06:05

mehboob110233
Messages: 55
Registered: February 2014
Location: Pakistan

Member

Thanks sir,
Its helpfull form me

select ADD_MONTHS('03-apr-2004',
TRUNC(MONTHS_BETWEEN('26-oct-2015',
'03-apr-2004') / 12) * 12)from dual

Report message to a moderator

Re: Annual leave of the year [message #643603 is a reply to message #643601]

Tue, 13 October 2015 06:27

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Note that '03-apr-2004' is not a date it is a string:

SQL> select to_date('03-apr-2004') from dual;
select to_date('03-apr-2004') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

cookiemonster's suggested solution was something like:

SQL> select to_date(to_char(to_date('03-apr-2004','DD-mon-YYYY'),'DD-mon-')||'2015',
  2                 'DD-mon-YYYY') dt
  3  from dual
  4  /
DT
-----------
2015-apr-03

Report message to a moderator

Previous Topic:	Non database item
Next Topic:	Oracle Forms Master Detail detail

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 17 21:36:46 CDT 2024