oracle 时间常用函数和注意事项

 --判断是不是闰年
  FUNCTION ISLEAPYEAR(A_YEAR NUMBER) RETURN VARCHAR2 IS
  BEGIN
    IF MOD(A_YEAR, 400) = 0 THEN
      RETURN '1';
    ELSE
      IF MOD(A_YEAR, 4) = 0 AND MOD(A_YEAR, 100) <> 0 THEN
        RETURN '1';
      ELSE
        RETURN '0';
      END IF;
    END IF;
  END ISLEAPYEAR;

  --获取每月的天数
  FUNCTION DAYS_EVERYMONTH(A_YEAR NUMBER, A_MONTH NUMBER) RETURN NUMBER IS
    P_DAYS NUMBER := 0;
  BEGIN
    CASE TRUE
      WHEN A_MONTH = 1 OR A_MONTH = 3 OR A_MONTH = 5 OR A_MONTH = 7 OR
       A_MONTH = 8 OR A_MONTH = 10 OR A_MONTH = 12 THEN
        P_DAYS := 31;
      WHEN A_MONTH = 4 OR A_MONTH = 6 OR A_MONTH = 9 OR A_MONTH = 11 THEN
        P_DAYS := 30;
      WHEN A_MONTH = 2 AND ISLEAPYEAR(A_YEAR) = '1' THEN
        P_DAYS := 29;
      WHEN A_MONTH = 2 AND ISLEAPYEAR(A_YEAR) = '0' THEN
        P_DAYS := 28;
      ELSE
        NULL;
    END CASE;
    RETURN P_DAYS;

--注意时间判断问题：
AND P.V_INDATE BETWEEN
TO_DATE(v_begindate, 'yyyy/MM/dd hh24:mi:ss') AND
TO_DATE(v_enddate, 'yyyy/MM/dd hh24:mi:ss')) a

 AND TO_DATE(TO_CHAR(D_DEFECTDATE, 'YYYY/MM/DD'), 'YYYY/MM/DD') >=
         TO_DATE(V_D_DEFECTDATE_B, 'YYYY/MM/DD')
     AND TO_DATE(TO_CHAR(D_DEFECTDATE, 'YYYY/MM/DD'), 'YYYY/MM/DD') <=
         TO_DATE(V_D_DEFECTDATE_E, 'YYYY/MM/DD')

v_enddate := V_EDATE || '/' || V_DAT || ' 23:59:59'; 
如果不添加分秒时，或者用between可能会不包含当天的数据查询