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