平時在項(xiàng)目中會經(jīng)常遇到需要用某個日期（如當(dāng)前系統(tǒng)時間）來獲取該日期前后的特定日期货岭，下面列出一些常用的公式。
? sysdate為系統(tǒng)當(dāng)前日期
基礎(chǔ)函數(shù)
- 字符串類型轉(zhuǎn)換成日期類型
select to_date('2016-06-15','yyyy-mm-dd') from dual -- 2016-6-15
- 日期類型轉(zhuǎn)換成字符串類型
select to_char(sysdate,'yyyy-mm-dd') from dual -- 系統(tǒng)當(dāng)前日期
拓展公式
- 當(dāng)年年初日期
select trunc(to_date('2016-06-15','yyyy-mm-dd'),'year') from dual -- 2016-1-1
- 當(dāng)年年末日期
select add_months(trunc(to_date('2016-06-15','yyyy-mm-dd'),'year'),12)-1 from dual -- 2016-12-31
- 當(dāng)前日期上月月初
select add_months(last_day(to_date('2016-06-15','yyyy-mm-dd'))+1,-2) from dual -- 2016-5-1
select add_months(last_day(to_date('2016-01-15','yyyy-mm-dd'))+1,-2) from dual -- 2015-12-1
- 當(dāng)前日期上月月末
select add_months(last_day(to_date('2016-06-15','yyyy-mm-dd')),-1) from dual -- 2016-5-31
select add_months(last_day(to_date('2016-01-15','yyyy-mm-dd')),-1) from dual -- 2015-12-31
- 當(dāng)前日期上月年初
select trunc(to_date(add_months(last_day(to_date('2016-06-15','yyyy-mm-dd')),-1)),'year') from dual -- 2016-1-1
select trunc(to_date(add_months(last_day(to_date('2016-01-15','yyyy-mm-dd')),-1)),'year') from dual -- 2015-1-1
- 當(dāng)前日期上月年末
select add_months(trunc(to_date(add_months(last_day(to_date('2016-06-15','yyyy-mm-dd')),-1)),'year'),12)-1 from dual -- 2016-12-31
select add_months(trunc(to_date(add_months(last_day(to_date('2016-01-15','yyyy-mm-dd')),-1)),'year'),12)-1 from dual -- 2015-12-31
- 當(dāng)前日期上年年初
select add_months(trunc(to_date('2016-06-15','yyyy-mm-dd'),'year'),-12) from dual -- 2015-1-1
- 當(dāng)前日期上年年末
select trunc(to_date('2016-06-15','yyyy-mm-dd'),'year')-1 from dual -- 2015-12-31
- 當(dāng)前日期下月月初
select add_months(last_day(to_date('2016-06-15','yyyy-mm-dd'))+1,0) from dual -- 2016-7-1
select add_months(last_day(to_date('2015-12-15','yyyy-mm-dd'))+1,0) from dual -- 2016-1-1
- 當(dāng)前日期下月月末
select add_months(last_day(to_date('2016-06-15','yyyy-mm-dd')),1) from dual -- 2016-7-31
select add_months(last_day(to_date('2015-12-15','yyyy-mm-dd')),1) from dual -- 2016-1-31
- 當(dāng)前日期下月年初
select trunc(to_date(add_months(last_day(to_date('2016-06-15','yyyy-mm-dd')),1)),'year') from dual -- 2016-1-1
select trunc(to_date(add_months(last_day(to_date('2015-12-15','yyyy-mm-dd')),1)),'year') from dual -- 2016-1-1
- 當(dāng)前日期下月年末
select add_months(trunc(to_date(add_months(last_day(to_date('2016-06-15','yyyy-mm-dd')),1)),'year'),12)-1 from dual -- 2016-12-31
select add_months(trunc(to_date(add_months(last_day(to_date('2015-12-15','yyyy-mm-dd')),1)),'year'),12)-1 from dual -- 2016-12-31
- 當(dāng)前日期下年年初
select add_months(trunc(to_date('2016-06-15','yyyy-mm-dd'),'year'),12) from dual -- 2017-1-1
- 當(dāng)前日期下年年末
select add_months(trunc(to_date('2016-06-15','yyyy-mm-dd'),'year')-1,24) from dual -- 2017-12-31
