平时在项目中会经常遇到需要用某个日期（如当前系统时间）来获取该日期前后的特定日期，下面列出一些常用的公式。
✳ sysdate为系统当前日期
基础函数
- 字符串类型转换成日期类型
select to_date('2016-06-15','yyyy-mm-dd') from dual -- 2016-6-15
- 日期类型转换成字符串类型
select to_char(sysdate,'yyyy-mm-dd') from dual -- 系统当前日期
拓展公式
- 当年年初日期
select trunc(to_date('2016-06-15','yyyy-mm-dd'),'year') from dual -- 2016-1-1
- 当年年末日期
select add_months(trunc(to_date('2016-06-15','yyyy-mm-dd'),'year'),12)-1 from dual -- 2016-12-31
- 当前日期上月月初
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
- 当前日期上月月末
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
- 当前日期上月年初
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
- 当前日期上月年末
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
- 当前日期上年年初
select add_months(trunc(to_date('2016-06-15','yyyy-mm-dd'),'year'),-12) from dual -- 2015-1-1
- 当前日期上年年末
select trunc(to_date('2016-06-15','yyyy-mm-dd'),'year')-1 from dual -- 2015-12-31
- 当前日期下月月初
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
- 当前日期下月月末
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
- 当前日期下月年初
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
- 当前日期下月年末
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
- 当前日期下年年初
select add_months(trunc(to_date('2016-06-15','yyyy-mm-dd'),'year'),12) from dual -- 2017-1-1
- 当前日期下年年末
select add_months(trunc(to_date('2016-06-15','yyyy-mm-dd'),'year')-1,24) from dual -- 2017-12-31
