Oacle 日期函数的应用

?

Oracle 使用 months_between(ddd.s_date, S.VALID_BEGIN_DATE)? 来计算月份的差值。

得出的是带小数的值。

?

?

?

?

SELECT P.PILOT_ID basicInfoId,    P.CLERK_CODE clerkCode,       P.PILOT_NAME pilotName,       S.RETRAINING_PLACE comId,       U.UNIT_NAME unitName,       S.MONTH1 month1,       S.MONTH2 month2,       S.VALID_BEGIN_DATE validBeginDate,       ddd.s_date,       months_between(ddd.s_date, S.VALID_BEGIN_DATE) AS monthnum,       CASE         WHEN SUBSTR(NVL(S.TECHNICAL_STANDARD, '21'), 0, 1) = '1' THEN          '机长'         ELSE          '副驾驶'       END technicalStandard,       S.NEXT_SCIAMACHY nextSciamachy,       S.NEXT_SCIAMACHY_TEACHER nextSciamachyTeacher,       S.TEACHER_STANDARD teacherStandard,       S.SKILLED_INSPECTOR_STAND_DATE skilledInspectorStandDate,       S.OFFICE_INSPECTOR_STAND_DATE officeInspectorStandDate,       S.VALID_END_DATE validEndDate,       S.LICENCE_END_DATE licenceEndDate,       S.NEXT_TEACHER_NUM nextTeacherNum       --S.MAIN_AC_TYPE_CODE mainAcTypeCode  FROM sim_v_pilot P, dc.v_pilot_licence_standard S, flight.BD_AERO_UNIT U, dc.bd_ac_type a,     (   SELECT max(m.s_date) AS s_date, n.pilot_id   FROM sim_training_sim m LEFT JOIN sim_student_assign n ON m.suite_id = n.suite_id   WHERE m.ac_type_code = 14 GROUP BY n.pilot_id   ) ddd        WHERE P.PILOT_ID = S.PILOT_ID   AND U.UNIT_CODE = SUBSTR(P.ORG_CODE, 0, 4)   AND S.MAIN_AC_TYPE_CODE = '14'   and s.main_ac_type_code = a.ac_type_code   AND P.WORK_TYPE = '5201'   AND S.MONTH1 IS NOT NULL   AND S.MONTH2 IS NOT NULL   AND ddd.pilot_id = P.Pilot_Id   AND S.NEXT_SCIAMACHY = 1   AND (to_char(ddd.s_date, 'mm') - S.MONTH1 > 1 OR to_char(ddd.s_date, 'mm') - S.MONTH1 < -1)

?

?

?

?

?