إنتقال للمحتوى

  • تسجيل الدخول عبر الفيس بوك تسجيل الدخول عبر تويتر Log In with LinkedIn Log In with Google      تسجيل دخول    
  • إنشاء حساب

صورة
- - - - -

Calculate Contracte Date From Sysdate Using Hire Date


2 رد (ردود) على هذا الموضوع

#1 oradev10g

oradev10g

    عضو

  • الأعضــاء
  • 1 مشاركة

تاريخ المشاركة 24 December 2006 - 05:54 PM

i would like to calculate when is the renewal date for each employee before two months of the renewal time exactly and i only have hiredate, i don't have last date of contract renewal in my table and i should have a form that get populated with the contracts that should be renewed before two months from sysdate .. i want an accurate way to do it .. i used this one but i am not sure if it is 100% accurate

is there a better way to do it .... main missing data is last renewal date( here is my problem) and i am not supposed to add it


select id, FIRST_NAME_ARB, employment_date, MOD(months_between ( trunc(sysdate) ,trunc(employment_date))/12,1) from PRS_EMPLOYEE_PROFILE
where
MOD(months_between ( trunc(sysdate) ,trunc(employment_date))/12,1) > .8

what do you think is?



#2 egyhamada

egyhamada

    مشترك

  • الأعضــاء
  • 194 مشاركة
  • البـلـد: Country Flag

تاريخ المشاركة 27 December 2006 - 11:00 AM

to have the employees of exactly 2 months you have to type

Where MOD(months_between ( trunc(sysdate) ,trunc(employment_date))/12,1) = 0.83333333333

#3 amsh

amsh

    عضو

  • الأعضــاء
  • 20 مشاركة

تاريخ المشاركة 28 December 2006 - 08:39 AM

السلام عليكم ورحمة الله
I haven't realize What you need exactly but your syntx have some mistakes :)
1.the Trunc want other argument in its paranthesis weth comma separater
2.the function MOD takes months_between(trunc(sysdate) ,trunc(employment_date))/12)
as first argument and(1)as secound argument so you should write it sa folllow:
months_between (sysdate ,employment_date),12.1)
at last the I think the next conditions will help you :)
1].WHERE EMPLOYMENT_DATE=ADD_MONTHS(SYSDATE,-2) --if ypu want it exactly (as you wrote)
--By a day
---
2].WHERE TRUNC (SYSDATE-EMPLOYEMENT_DATE)=2
--
3].WHERE TROUNC(MONTHS_BETWEEN(SYSDATE,EMPLOYMENT_DATE))=2
---
With best wishs
Abo_Obaida