1.Months_between()
//返回两个时间值的月数差
SELECT MONTHS_BETWEEN(to_date('20201203','yyyymmdd'),(to_date('20190524','yyyymmdd'))) FROM dual
//将返回值向下取整
SELECT Floor(MONTHS_BETWEEN(to_date('20201203','yyyymmdd'),(to_date('20190524','yyyymmdd')))) FROM dual
//将返回值向上取整
SELECT CEIL (MONTHS_BETWEEN(to_date('20201203','yyyymmdd'),(to_date('20190524','yyyymmdd')))) FROM dual
2.Add_months(日期,所加月数)
//返回相应时间值x个月后的时间
SELECT ADD_MONTHS(to_date('20200524','yyyymmdd'),5) FROM dual
3.next_day(日期,)
//返回未来一周内的第几天/星期几
SELECT next_day(to_date('20221025','yyyymmdd'),'星期一') FROM dual
4.last_day(日期)
//返回日期对应月的最后一天
SELECT last_day(to_date('20221025','yyyymmdd')) FROM dual
5.trunc
①trunc(日期,year/month)
//返回当年/月的第一天
SELECT trunc(to_date('20220415','yyyymmdd'),'month') FROM dual
②trunc还可以用于数字截取
trunc(a,b)
//a为操作数,b为截取的位数(b为正,则小数点向右截取相应位数;b为负,则小数点向左截取相应位数)
SELECT trunc(2236,-3) FROM dual
返回2000
6.round(日期,year/month)
//日期的四舍五入,返回该年(月)的第一天(舍)或最后一天(入)
SELECT round(to_date('20221025','yyyymmdd'),'year') FROM dual
PS:round(a,b),a表示要四舍五入的数,b表示要保留的位数(b取正数表示小数点后保留几位,b取负数表示小数点前保留几位)
SELECT round(55485455.2564,2) FROM dual
返回55485455.26
SELECT round(55485455.2564,-2) FROM dual
返回55485500