日期操作

1.判断是否为闰年

  select to_char(last_day(add_months(trunc(to_date('2000-03-03','yyyy-mm-dd'),'Y'),1)),'DD') from dual

 

2.确定一年内的天数

  select add_months(trunc(sysdate,'Y'),-12),trunc(sysdate,'Y'), trunc(sysdate,'Y') - add_months(trunc(sysdate,'Y'),-12) from dual

 

3.从日期中提取时间的各部分

  select to_number(to_char(sysdate,'yyyy')) 年,
       to_number(to_char(sysdate,'mm')) 月,
       to_number(to_char(sysdate,'dd')) 日,
       to_number(to_char(sysdate,'hh24')) 时,
       to_number(to_char(sysdate,'mi')) 分,
       to_number(to_char(sysdate,'ss')) 秒
        from dual

 

4.确定某个月的第一天和最后一天

  select trunc(sysdate,'Y'),last_day(trunc(sysdate,'Y')) from dual

 

5.查出所有是星期五的日期

  ①

     with x as(
    select trunc(sysdate,'Y') + level - 1 dy from dual
    connect by level <= add_months(trunc(sysdate,'Y'),12)-trunc(sysdate,'Y')
    )
    select * from x where to_char(dy,'DAY') = '星期五'

  ②select trunc(sysdate,'Y') + id - 1 from t500

     where to_char(trunc(sysdate,'Y') + id -     1,'DAY') = '星期五'

 

6.查出某月第一个星期一日期和该月最后一个星期一的日期

  ①with x as(
   select trunc(sysdate,'mm') + level -1 dy from dual
   connect by level <= add_months(trunc(sysdate,'mm'),1) - trunc(sysdate,'mm')
  )
  select min(dy),max(dy) from x where to_char(dy,'DAY') = '星期五'

  ②select next_day(trunc(sysdate,'mm')-1,'星期一') first_monday,
       next_day(last_day(trunc(sysdate,'mm'))-7,'星期一') last_monday
from dual

 

7.制作日期

with x as(
select * from (
 select to_char(trunc(sysdate,'mm') + level - 1,'iw') wk,
        to_char(trunc(sysdate,'mm') + level - 1,'dd') dm,
        to_number(to_char(trunc(sysdate,'mm') + level - 1,'d')) dw,
        to_char(trunc(sysdate,'mm') + level - 1,'mm') curr_mth,
        to_char(sysdate,'mm') mth
 from dual    
 connect by level <= 31  
 )
 where curr_mth = mth
)      
select max(case dw when 2 then dm end) 星期一,
       max(case dw when 3 then dm end) 星期二,
       max(case dw when 4 then dm end) 星期三,
       max(case dw when 5 then dm end) 星期四,
       max(case dw when 6 then dm end) 星期五,
       max(case dw when 7 then dm end) 星期六,
       max(case dw when 1 then dm end) 星期七 
from x
group by wk order by wk  

 

8.列出一年中每个季度开始日期和结束日期

 ①with x as(
 select add_months(trunc(sysdate,'Y'),(level*3)-1) fday,last_day(add_months(trunc(sysdate,'Y'),(level*3)-1)) lday from dual
 connect by level <= 12
)
select * from x where fday <= add_months(trunc(sysdate,'Y'),12)

 

 ②

select rownum qtr,
       add_months(trunc(sysdate,'Y'),(rownum-1)*3) q_start,
       add_months(trunc(sysdate,'Y'),(rownum*3)-1) q_end
from emp
where rownum <= 4

 

9.确定给定季度的开始日期和结束日期

 select add_months(q_end,-2) q_start,
       last_day(q_end) q_end
from (
      select to_date(substr(yrq,1,4) || mod(yrq,10)*3,'yyyymm') q_end
      from(
      select 20051 yrq from dual
      union all
      select 20052 yrq from dual      
      union all
      select 20053 yrq from dual
      union all
      select 20054 yrq from dual
     ) x
    ) y 

 

 

10.填充丢失的日期

   with temp as(
  select add_months(first_month,level-1) months from(
  select trunc(min(hiredate),'mm') first_month,trunc(max(hiredate),'mm') last_month from emp
 ) connect by level <= months_between(last_month,first_month)+1
)
select months,count(hiredate) from temp,emp
where temp.months = trunc(hiredate(+),'mm')
 group by months order by months  

 

11.按照给定的时间单位进行查找

  select ename from emp
 where rtrim(to_char(hiredate,'month')) in ('2月','12月')
      or rtrim(to_char(hiredate,'day')) = '星期二'

 

12.使用日期的特殊部分比较记录

  select a.ename || 'was hired on the same month and weekday as ' || b.ename as msg
from emp a,emp b
where to_char(a.hiredate,'DMON') = to_char(b.hiredate,'DMON')
      and a.empno < b.empno
      order by a.ename

 

13.识别重叠的日期范围

  select a.empno,a.ename,'Project ' || b.proj_id || ' overlaps project ' || a.project_id as msg
from emp_object a,emp_object b
where a.empno = b.empno
  and b.project_start >= a.project_start
  and b.project_end >= b.project_end
  and b.id != a.id

 

14.

1 、SELECT to_date(replace('Sat Jul 10 13:06:00 CST 2010','CST',''),
'DY MON dd HH24:mi:ss yyyy', 'NLS_DATE_LANGUAGE   =   American ') FROM DUAL