日期sql

20.查看入职日期的月初
select hiredate,trunc(‘2017-12-12’,‘mm’) from aa_lxx ;

21.查询年月日时分秒月初年初周初月末下月初年初周几月份
select to_number(to_char(hiredate,‘hh24’)) as 时,
to_number(to_char(hiredate,‘mi’)) as 分,
to_number(to_char(hiredate,‘ss’)) as 秒,
to_number(to_char(hiredate,‘dd’)) as 日,
to_number(to_char(hiredate,‘mm’)) as 月,
to_number(to_char(hiredate,‘yyyy’)) as 年,
to_number(to_char(hiredate,‘ddd’)) as 年内第几天,
trunc(hiredate,‘dd’) as 一天之始,
trunc(hiredate,‘day’) as 周初,
trunc(hiredate,‘mm’) as 月初,
last_day(hiredate) as 月末,
add_months(trunc(hiredate,‘mm’),1) as 下月初,
trunc(hiredate,‘yy’) as 年初,
to_char(hiredate,‘day’) as 周几,
to_char(hiredate,‘month’) as 月份
from (select hiredate + 30 / 24 / 60 / 60 + 20 / 24 / 60 + 5 / 24 as hiredate
from aa_lxx
where rownum = ‘1’)

22.计算该年是否为闰年
select hiredate,to_char(last_day(add_months(trunc(hiredate, ‘yy’), 1)), ‘DD’) from aa_lxx
23.一年中的所有星期五
select dy, to_char(dy, ‘day’)
from (select trunc(sysdate, ‘Y’) + (level - 1) dy
from dual connect by level <= add_months(trunc(sysdate, ‘y’), 12) - trunc(sysdate, ‘y’))
where to_char(dy, ‘d’) = 6

24.当月内第一个星期一与最后一个星期一
select next_day(trunc(hiredate,‘mm’)-1,2), next_day(last_day(trunc(hiredate,‘mm’))-7,2) from aa_lxx

25.指定月份的日历
select max(case 周几 when 2 then 日期 end) 周一,
max(case 周几 when 3 then 日期 end) 周二,
max(case 周几 when 4 then 日期 end) 周三,
max(case 周几 when 5 then 日期 end) 周四,
max(case 周几 when 6 then 日期 end) 周五,
max(case 周几 when 7 then 日期 end) 周六,
max(case 周几 when 8 then 日期 end) 周日
from (select to_char(t,‘iw’) 所在周,to_char(t,‘dd’) 日期,to_number(to_char(t,‘d’)) 周几 from (select yc + (level - 1) t
from (select trunc(to_date(‘2017-12-15’, ‘yyyy-mm-dd’), ‘mm’) yc,
add_months(trunc(to_date(‘2017-12-15’, ‘yyyy-mm-dd’), ‘mm’),1) xyc from dual)
connect by level <= (xyc - yc)))
group by 所在周 order by 所在周

26.全年日历

select case when lag(所在月份) over(order by 所在周) = 所在月份 then
null else 所在月份 end as 月份, 所在周,
max(case 周几 when 2 then 日期 end) 周一,
max(case 周几 when 3 then 日期 end) 周二,
max(case 周几 when 4 then 日期 end) 周三,
max(case 周几 when 5 then 日期 end) 周四,
max(case 周几 when 6 then 日期 end) 周五,
max(case 周几 when 7 then 日期 end) 周六,
max(case 周几 when 8 then 日期 end) 周日
from (select 日期, 所在月份, case when 所在月份 = ‘12’ and 所在周 = ‘01’ then
‘53’ else 所在周 end as 所在周, to_number(to_char(日期, ‘d’)) 周几
from (select 日期,to_char(日期, ‘mm’) as 所在月份, to_char(日期, ‘iw’) as 所在周
from (select 本年初 + (level - 1) as 日期 from (select trunc(to_date(年份, ‘yyyy’)) as 本年初,
add_months(trunc(to_date(年份, ‘yyyy’)),12) as 下年初 from (select 2013 as 年份 from dual))
connect by level <= 下年初 - 本年初))) group by 所在月份,所在周 order by 2