SQL常⽤⽇期查询语句及显⽰格式设置
1.查询当前年、⽉、周相关时间
1.1.查询当前年份
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--查询当前年份最后⼀位
1.2.查询当前年份第⼏天
SELECT TO_CHAR(SYSDATE,'DDD') AS DAYS FROM DUAL
1.3.查询当前⽉份第⼏天
SELECT TO_CHAR(SYSDATE,'DD') AS DAYS FROM DUAL
1.4.查询当前周的星期⼏
SELECT TO_CHAR(SYSDATE,'dy') AS WEEK FROM DUAL
SELECT TO_CHAR(SYSDATE,'day') AS WEEK FROM DUAL
1.5.查询当前年的第⼏天：
SELECT TO_CHAR(SYSDATE,'DDD') AS DAYS FROM DUAL
2.查询当前第⼏季度
SELECT TO_CHAR(SYSDATE,'Q') AS JD FROM DUAL
3.查询当前在⼀年内是第⼏周
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
4.查询当前某年某⽉有多少天
SELECT TO_CHAR(last_day(to_date('2019/02/01','YYYY/MM/DD')),'DD') AS MONTH_DAYS_NUMBER FROM DUAL
5.查询当前的⽇期格式
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：下午：星期四
6.查询距离当前时间⼀段时间间隔的时间，当前时间之前⽤“-”当前时间之后⽤“+”
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天前时间
常用的sql查询语句有哪些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 --查询当前时间参数年、⽉、⽇、时、分、秒乘以⼀个数字倍数之前的时间到此这篇关于SQL常⽤⽇期查询语句及显⽰格式设置的⽂章就介绍到这了。希望对⼤家的学习有所帮助，也希望⼤家多多⽀持。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。