比如求TEST中trxn_dt在指定的时间START_DT和END_DT中并且trxn_dt为该月的最后一天:select * from test t where t.trxn_dt=last_dt(t.trxn_dt) and t.trxn_dt between start_dt and t.end_dt
select * from test t where t.trxn_dt=last_dt(t.trxn_dt) and t.trxn_dt between start_dt and end_dt
不知道你是否这个意思,不是的话 给几组数据 举例说明下with t as( select to_date('2014/02/10','yyyy/mm/dd') tdate,1 id from dual union all select to_date('2014/02/15','yyyy/mm/dd') tdate,2 id from dual union all select to_date('2014/02/28','yyyy/mm/dd') tdate,3 id from dual union all select to_date('2014/03/10','yyyy/mm/dd') tdate,4 id from dual ) select * from t where tdate in (select max(tdate) from (select last_day(add_months(tdate, 0)) mm, id, tdate from t where tdate between to_date('2014/02/10', 'yyyy/mm/dd') and to_date('2014/03/14', 'yyyy/mm/dd')) group by mm)
SELECT * FROM TEST T WHERE T.TRXN_DT=LAST_DAY(T.TRXN_DT) AND TO_CHAR(T.TRXN_DT,'MM') IN ('01','02','03')
SQL> select trunc(sysdate,'mm') from dual;TRUNC(SYSDATE,'MM') ------------------- 2014-02-01 00:00:00SQL> select trunc(sysdate,'mm')-1 from dual;TRUNC(SYSDATE,'MM') ------------------- 2014-01-31 00:00:00
比如求TEST中trxn_dt在指定的时间START_DT和END_DT中并且trxn_dt为该月的最后一天:select * from test t where t.trxn_dt=last_dt(t.trxn_dt) and t.trxn_dt between start_dt and t.end_dt
select * from test t where t.trxn_dt=last_dt(t.trxn_dt) and t.trxn_dt between start_dt and end_dt
select to_date('2014/02/10','yyyy/mm/dd') tdate,1 id from dual
union all
select to_date('2014/02/15','yyyy/mm/dd') tdate,2 id from dual
union all
select to_date('2014/02/28','yyyy/mm/dd') tdate,3 id from dual
union all
select to_date('2014/03/10','yyyy/mm/dd') tdate,4 id from dual
)
select * from t where tdate in (select max(tdate)
from (select last_day(add_months(tdate, 0)) mm, id, tdate
from t
where tdate between to_date('2014/02/10', 'yyyy/mm/dd') and
to_date('2014/03/14', 'yyyy/mm/dd'))
group by mm)
SELECT * FROM TEST T WHERE T.TRXN_DT=LAST_DAY(T.TRXN_DT) AND TO_CHAR(T.TRXN_DT,'MM') IN ('01','02','03')
-------------------
2014-02-01 00:00:00SQL> select trunc(sysdate,'mm')-1 from dual;TRUNC(SYSDATE,'MM')
-------------------
2014-01-31 00:00:00