Oracle PL SQL 日期日间操作备忘
select updatedate from trans_studycard t
where
to_date('2010-12-17 23:59:59','yyyy-mm-dd hh24:mi:ss') - updatedate >0
and to_date('2010-12-17 23:59:59','yyyy-mm-dd hh24:mi:ss') - updatedate < 3
order by updatedate
六种日期函数：
1. add_months(日期，number) 指定日期推迟number个月
2. last_day(日期) 指定日期当月的最后一天
3. new_time(日期，时区简写) 调整时区
4. next_day(日期，number) number表示周几，星期日是1，指定number的日期（一周内或一周后）
5. months_between(日期1，日期2) 日期1和日期2之间有几个月
6. sysdate 系统当期那日期和时间
update trans_exchangeapplication set applicationformdata = replace(applicationformdata,',','#')
where
/*
sselect to_char(TRANSACTIONTIME,'yyyy-MM-dd') as StatisticDataTime,
sum(decode(TRANSACTIONTYPE,'1',TRANSACTIONMONYAMOUNT,0)) as DepositCount,
sum(decode(TRANSACTIONTYPE,'3',TRANSACTIONMONYAMOUNT,0)) as ConsumCount
from trans_transactionhistory t
where months_between(TRANSACTIONTIME,to_date('2010-12-1','yyyy-MM-dd'))<1
group by to_char(TRANSACTIONTIME,'yyyy-MM-dd')
order by StatisticDataTime desc
*/
/*按月，2010年的，按月
select to_char(TRANSACTIONTIME,'yyyy-MM') as StatisticDataTime,
sum(decode(TRANSACTIONTYPE,'1',TRANSACTIONMONYAMOUNT,0)) as DepositCount,
sum(decode(TRANSACTIONTYPE,'3',TRANSACTIONMONYAMOUNT,0)) as ConsumCount
from trans_transactionhistory t
where to_char(TRANSACTIONTIME,'yyyy')='2010'
group by to_char(TRANSACTIONTIME,'yyyy-MM')
order by StatisticDataTime desc
* */
/*季报
select case when to_char(TRANSACTIONTIME,'q')=1 then '第一季度'
when to_char(TRANSACTIONTIME,'q')=2 then '第二季度'
when to_char(TRANSACTIONTIME,'q')=3 then '第三季度'
when to_char(TRANSACTIONTIME,'q')=4 then '第四季度' end as StatisticDataTime,
sum(decode(TRANSACTIONTYPE,'1',TRANSACTIONMONYAMOUNT,0)) as DepositCount,
sum(decode(TRANSACTIONTYPE,'3',TRANSACTIONMONYAMOUNT,0)) as ConsumCount
from trans_transactionhistory t
where to_char(TRANSACTIONTIME,'yyyy')='2010'
group by to_char(TRANSACTIONTIME,'q')
order by StatisticDataTime
*/
/*年报
select to_char(TRANSACTIONTIME,'yyyy') as StatisticDataTime,
sum(decode(TRANSACTIONTYPE,'1',TRANSACTIONMONYAMOUNT,0)) as DepositCount,
sum(decode(TRANSACTIONTYPE,'3',TRANSACTIONMONYAMOUNT,0)) as ConsumCount
from trans_transactionhistory t
where to_number(to_char(TRANSACTIONTIME,'yyyy'))>= 2010 and to_number(to_char(TRANSACTIONTIME,'yyyy'))<=2011
group by to_char(TRANSACTIONTIME,'yyyy')
order by StatisticDataTime desc
*/
