oracle 日期操作语句总结

SELECT TO_CHAR(SYSDATE, 'YYYY' ) AS YEAR FROM DUAL --查询当前年份

SELECT TO_CHAR(SYSDATE, 'YYY' ) AS YEAR FROM DUAL --查询当前年份后两位

SELECT TO_CHAR(SYSDATE, 'YY' ) AS YEAR FROM DUAL --查询当前年份最后两位

SELECT TO_CHAR(SYSDATE, 'Y' ) AS YEAR FROM DUAL --查询当前年份最后一位

SELECT TO_CHAR(SYSDATE, 'DDD' ) AS DAYS FROM DUAL

SELECT TO_CHAR(SYSDATE, 'DD' ) AS DAYS FROM DUAL

SELECT TO_CHAR(SYSDATE, 'dy' ) AS WEEK FROM DUAL

SELECT TO_CHAR(SYSDATE, 'day' ) AS WEEK FROM DUAL

SELECT TO_CHAR(SYSDATE, 'Q' ) AS JD FROM DUAL

SELECT TO_CHAR(SYSDATE, 'IW' ) AS WEEKNUM FROM DUAL

SELECT TO_CHAR(SYSDATE, 'WW' ) AS WEEKNUM FROM DUAL

SELECT TO_CHAR(SYSDATE, 'W' ) AS WEEKNUM FROM DUAL

SELECT TO_CHAR(last_day(to_date( '2019/02/01' , 'YYYY/MM/DD' )), 'DD' ) AS MONTH_DAYS_NUMBER FROM DUAL

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD' ) AS MONTH FROM DUAL --获取当前日期的'YYYY/MM/DD'格式：2020/01/02

SELECT TO_CHAR(SYSDATE, 'YYYYMMDD' ) AS MONTH FROM DUAL --获取当前日期的'YYYYMMDD'格式：20200102

SELECT TO_CHAR(SYSDATE, 'YYYYMMDD HH:MI:SS' ) AS now FROM DUAL --获取当前时间，精确到秒的12小时制格式：20200102 03:18:25

SELECT TO_CHAR(SYSDATE, 'YYYYMMDD HH24:MI:SS' ) AS now FROM DUAL --获取当前时间，精确到秒24小时制的格式：20200102 15:18:13

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS:PM:DY' ) AS now FROM DUAL --获取当前时间的格式，显示上、下午和星期几：2020/01/02 15:17:59:下午：星期四

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS' ) AS NOW,TO_CHAR(SYSDATE - INTERVAL '7' second , 'YYYY/MM/DD HH24:MI:SS' ) AS PAST FROM DUAL --查询当前时间的7秒前时间

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS' ) AS NOW,TO_CHAR(SYSDATE - INTERVAL '7' minute , 'YYYY/MM/DD HH24:MI:SS' ) AS PAST FROM DUAL --查询当前时间的7分钟前时间

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS' ) AS NOW,TO_CHAR(SYSDATE - INTERVAL '7' hour , 'YYYY/MM/DD HH24:MI:SS' ) AS PAST FROM DUAL --查询当前时间的7小时前时间

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS' ) AS NOW,TO_CHAR(SYSDATE - INTERVAL '7' day , 'YYYY/MM/DD HH24:MI:SS' ) AS PAST FROM DUAL --查询当前时间的7天前时间

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS' ) AS NOW,TO_CHAR(SYSDATE - INTERVAL '7' month , 'YYYY/MM/DD HH24:MI:SS' ) AS PAST FROM DUAL --查询当前时间的7月前时间

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS' ) AS NOW,TO_CHAR(SYSDATE - INTERVAL '7' year , 'YYYY/MM/DD HH24:MI:SS' ) AS PAST FROM DUAL --查询当前时间的7年前时间

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS' ) AS NOW,TO_CHAR(SYSDATE - 8*INTERVAL '7' hour , 'YYYY/MM/DD HH24:MI:SS' ) AS PAST FROM DUAL --查询当前时间参数年、月、日、时、分、秒乘以一个数字倍数之前的时间