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
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天前时间
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常用日期查询语句及显示格式设置的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持。
