Oracle对日期进行操作（oracle 日期字段）

这篇文章的主要内容是Oracle数据库中对日期的基本操作，包含了常用的日期取值函数以及处理技巧。

1.sysdate能得到的消息以及常用的日期取值公式

select born as 生日,trunc(born,'mm') as 月初 from emp

select born,

to_number(to_char(born,'hh24')) 时,

to_number(to_char(born,'mi')) 分,

to_number(to_char(born,'ss')) 秒,

to_number(to_char(born,'dd')) 日,

to_number(to_char(born,'mm')) 月,

to_number(to_char(born,'yyyy')) 年,

to_number(to_char(born,'ddd')) 年内第几天,

trunc(born,'dd')一天之始,

trunc(born,'day')周初,

trunc(born,'mm')月初,

last_day(born)月末,

add_months(trunc(born,'mm'),1)下月初,

trunc(born,'yy')年初,

to_char(born,'day')周几,

to_char(born,'month')月份,

from emp where rownum <=1

取一个月的数据：

with t as

(select to_date('1995-08-02 10:00:00','yyyy-mm-dd hh24:mi:ss') as d1,

to_date('1995-08-21 09:00:00','yyyy-mm-dd hh24:mi:ss') as d2

from dual

)

select d1,d2

from t

where d1 >= trunc(d2,'mm')

and d1 < add_months(trunc(d2,'mm'),1);

2.INTERVAL

INTERVAL类型中保存的是时间间隔信息，可以通过对应的 INTERVAL 函数得到 INTERVAL类型的数据。

select interval '2' year as "year",

interval '50' month as "month",

interval '99' day as "day",

interval '80' hour as "hour",

interval '90' minute as "minute",

interval '3.15' second as "second",

interval '2 12:30:59' DAY to second as "DAY TO SECOND",

interval '13-3' year to month as "Year to month"

from dual;

当增加一个很复杂的时间段时，如上面的"02天12小时30分59秒"，通过interval实现显然更直观。

3.EXTRACT

与to_char一样，extract可以提取时间字段中的年、月、日、时、分、秒。不同的是，extract的返回值为number类型。

create table test as

select extract(year from systimestamp) as "YEAR",

extract(MONTH from systimestamp) as "MONTH",

extract(DAY from systimestamp) as "DAY",

extract(HOUR from systimestamp) as "HOUR",

extract(MINUTE from systimestamp) as "MINUTE",

extract(SECOND from systimestamp) as "SECOND"

FROM DUAL;

EXTRACT不能取date中的时、分、秒，示例如下：

select created,extract(day from created) as d

from dba_objects

where object_id = 2

select extract(hour from created) as h

from dba_objects

where object_id = 2

4.确定一年是否为闰年

若要判断一年是否为闰年，只要看二月末是哪一天就可以

select trunc(born,'y') 年初 --1995-01-01

from emp;

select add_months(trunc(born,'y'),1) 二月初 --1995-02-01

from emp;

select last_day(add_months(trunc(born,'y'),1)) 二月底 --1995-02-28

from emp;

5.周的计算

with x as

(select trunc(sysdate,'yy') + (level - 1) as 日期 from dual connect by level <= 8)

select 日期, --返回值1代表周日，2代表周一

to_char(日期,'d') as d,

to_char(日期,'day') as day,

next_day(日期,1) as 下个周日,

to_char(日期,'ww') as ww,

to_char(日期,'iw') as iw

from x;

6.确定一年内属于周内某一天的所有日期

案例：要求返回指定年份内的所有周五

方案：枚举全年信息，然后再过滤。

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 dy,to_char(dy,'day') as 周五 from x where to_char(dy,'d') = 6;

7.确定某月内第一个和最后一个“周内某天”的日期

select next_day(trunc(born,'mm') - 1,2) 第一个周一,

next_day(last_day(trunc(born,'mm')) - 7,2) 最后一个周一

from emp

8.创建本月日历

枚举指定月份所有的日期，并转换为对应的周信息，再按所在周做一次“行转列”即可。

with x1 as

(select to_date('2013-06-03','yyyy-mm-dd') as cur_date from dual),

x2 as

(select trunc(cur_date,'mm') as 月初,add_months(trunc(cur_date,'mm'),1) as 下月初 from x1),

x3 as

(select 月初 + (level - 1) as 日 from x2 connect by level <= (下月初 - 月初)),

x4 as

(select to_char(日,'iw') 所在周,to_char(日,'dd') 日期,to_number(to_char(日,'d')) 周几 from x3)

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 1 then 日期 end) 周日

from x4

group by 所在周

order by 所在周;

Oracle学习日记——高级查找

Oracle学习日记——给查询结果排序

MySQL学习系列之八——分组数据

MySQL学习系列之十五——MySQL插入数据