这篇文章介绍了SQL常用日期查询语句及显示格式设置，文中通过示例代码介绍的非常详细。对大家的学习或工作具有一定的参考借鉴价值，需要的朋友可以参考下
1.查询当前年、月、周相关时间
1.1.查询当前年份
|
1
2
3
4
|
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.查询当前年份第几天
|
1
|
SELECT TO_CHAR(SYSDATE,'DDD') AS DAYS FROM DUAL |
1.3.查询当前月份第几天
|
1
|
SELECT TO_CHAR(SYSDATE,'DD') AS DAYS FROM DUAL |
1.4.查询当前周的星期几
|
1
2
|
SELECT TO_CHAR(SYSDATE,'dy') AS WEEK FROM DUALSELECT TO_CHAR(SYSDATE,'day') AS WEEK FROM DUAL |
1.5.查询当前年的第几天：
|
1
|
SELECT TO_CHAR(SYSDATE,'DDD') AS DAYS FROM DUAL |
2.查询当前第几季度
|
1
|
SELECT TO_CHAR(SYSDATE,'Q') AS JD FROM DUAL |
3.查询当前在一年内是第几周
|
1
2
3
|
SELECT TO_CHAR(SYSDATE,'IW') AS WEEKNUM FROM DUALSELECT TO_CHAR(SYSDATE,'WW') AS WEEKNUM FROM DUALSELECT TO_CHAR(SYSDATE,'W') AS WEEKNUM FROM DUAL |
4.查询当前某年某月有多少天
|
1
|
SELECT TO_CHAR(last_day(to_date('2019/02/01','YYYY/MM/DD')),'DD') AS MONTH_DAYS_NUMBER FROM DUAL |
5.查询当前的日期格式
|
1
2
3
4
5
|
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD') AS MONTH FROM DUAL--获取当前日期的'YYYY/MM/DD'格式：2020/01/02SELECT TO_CHAR(SYSDATE,'YYYYMMDD') AS MONTH FROM DUAL--获取当前日期的'YYYYMMDD'格式：20200102SELECT TO_CHAR(SYSDATE,'YYYYMMDD HH:MI:SS') AS now FROM DUAL--获取当前时间，精确到秒的12小时制格式：20200102 03:18:25SELECT TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI:SS') AS now FROM DUAL--获取当前时间，精确到秒24小时制的格式：20200102 15:18:13SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS:PM:DY') AS now FROM DUAL--获取当前时间的格式，显示上、下午和星期几：2020/01/02 15:17:59:下午：星期四 |
6.查询距离当前时间一段时间间隔的时间，当前时间之前用“-”当前时间之后用“+”
|
1
2
3
4
5
6
7
|
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 --查询当前时间参数年、月、日、时、分、秒乘以一个数字倍数之前的时间 |
声明：本站所有资源都是由网友投稿发布，或转载各大下载站，请自行检测软件的完整性！
本站所有资源仅供学习与参考，请勿用于商业用途，否则产生的一切后果将由您自己承担！本站并非盈利站点，所有源码仅供学习交流使用，切勿上传使用并售卖
如有侵权请联系我们删除下架联系邮箱1685698671@qq.com。
