一、日期相关的内容
1.在Oracle中，date类型可以直接进行加减天数，而加减月份要用add_months函数：DEMO中用到的两张表。emp和T100
确定两个工作日期之间的工作天数。
desc emp;
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO NUMBER(4) Y
ENAME VARCHAR2(10) Y
JOB VARCHAR2(40) Y
MGR NUMBER(4) Y
HIREDATE DATE Y
SAL NUMBER(7,2) Y
COMM NUMBER(7,2) Y
DEPTNO NUMBER(2) Y
--用到的表
create table T500 as select level as ID from dual connect by level <=500
计算工作日
SELECT SUM(CASE
WHEN to_char(min_hd + T500.id - 1,
'DY',
'NLS_DATE_LANGUAGE = American') IN ('SAT', 'SUN') THEN
0
ELSE
1
END) AS 工作天数
FROM (select MIN(hiredate) as min_hd,MAX(hiredate) AS max_hd
FROM emp
where ename in ('JONES')) x, T500
where T500.id <= max_hd - min_hd + 1
--不计算工作
SELECT (max_hd-min_hd) +1 AS 天数
FROM ( SELECT MIN(hiredate) as min_hd, MAX(hredate）as max_hd from emp
WHERE ename IN ('JONES','ALLEN'）
2.计算一年中内有多少天是星期一，多少天是星期二之类的问题
2-1.获取年度信息
2-2.计算一年有多少天
2-3.生成列表
2-4.转换对应的星期标识
2-5.汇总
with x0 as
(select to_date('2018-01-01', 'yyyy-mm-dd') as 年初 from dual),
x1 as
(select 年初, add_months(年初, 12) as 下年初 from x0),
x2 as
(select 年初, 下年初, 下年初 - 年初 as 天数 from x1),
x3 as
(select 年初 + (LEVEL - 1) as 日期 from x2 connect by level <= 天数) ，
x4 as
(select 日期, to_char(日期, 'DY') as 星期 from x3)
select 星期, count(1) as 天数 from x4 group by 星期
3.常用的时间取值方式，可以提供参考
SELECT hiredate,
to_number(to_char(hiredate,'hh24')) 时,
to_number(to_char(hiredate,'mi')) 分,
to_number(to_char(hiredate,'ss')) 秒,
to_number(to_char(hiredate,'dd')) 日,
to_number(to_char(hiredate,'mm')) 月,
to_number(to_char(hiredate,'yyyy')) 年,
to_number(to_char(hiredate,'ddd')) 年内第几天,
trunc(hiredate,'dd') 一天之始,
trunc(hiredate,'day') 周初,
trunc(hiredate,'mm') 月初,
add_months(trunc(hiredate,'mm'),1) 下月初,
trunc(hiredate,'yy') 年初,
to_char(hiredate,'day') 周几,
to_char(hiredate,'month') 月份
FROM (SELECT hiredate + 30/24/60/60 + 20/24/60 + 5/24 as hiredate
from emp where rownum<=1)
查询结果
