sql oracle 获取当前日期的最后一天

语法

LAST_DAY 传入一个日期类型的变量，但会给你一个当月的最后一天的变量

 LAST_DAY(TO_DATE(year || '-' || SUBSTR(month, -2) || '-01', 'YYYY-MM-DD')) <= ?

应用实例

AssetValueSingleQT.spl

一个表中只存储的年和月，需要更具年月筛选小于指定日期，并且最新的一条记录

select
    COMPANY_CODE,
    YEAR,
    MONTH,
    FEE_TYPE,
    DAILY_FEE,
    MONTH_DAYS,
    DAILY_FEE * MONTH_DAYS as MONTH_FEE
from (SELECT COMPANY_CODE,
             YEAR,
             MONTH,
             FEE_TYPE,
             DAILY_FEE,
             to_number(to_char(LAST_DAY(to_date(year || SUBSTR(MONTH, -2), 'yyyyMM')), 'dd')) as MONTH_DAYS
      from SAP_FINANCE_FEE_MIX_MONTH
      where COMPANY_CODE = ?
        AND FEE_TYPE = ?
       AND LAST_DAY(TO_DATE(year || '-' || SUBSTR(month, -2) || '-01', 'YYYY-MM-DD')) <= TO_DATE(?, 'YYYY-MM-DD')
      order by YEAR desc,
               MONTH desc) day_fee
where ROWNUM = 1