一些常用查询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天前时间
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 --查询当前时间参数年、月、日、时、分、秒乘以一个数字倍数之前的时间