详细讲解Oracle数据库的“周数计算”

——日期计算 算第n周的第一天及最后一天是几号 by keynes

================================================

—— ww的算法为每年 月 日为第一周开始 date+ 为每一周结尾

—— 例如 为第一周的第一天 而第一周的最后一天为 + =

—— 公式 每周第一天 date + 周 *

—— 每周最后一天 date + 周 *

你会发现怎么编排格式都会跑掉

=========================================================================

——日期计算 算第n周的第一天及最后一天是几号 by keynes

=========================================================================

—— ww的算法为每年 月 日为第一周开始 date+ 为每一周结尾

—— 例如 为第一周的第一天 而第一周的最后一天为 + =

—— 公式 每周第一天 date + 周 *

—— 每周最后一天 date + 周 *

—— 如果以ww格式为主 第 周的起迄如下

asdb WF>select to_date（ yyyymmdd ） + * to_date（ yyyymmdd ） + * from dual

TO_DATE（ TO_DATE（

JAN JAN

asdb WF>select to_date（ yyyymmdd ） + * to_date（ yyyymmdd ） + * from dual

TO_DATE（ TO_DATE（

APR APR

Elapsed

—— 验证如下

asdb WF>select to_char（to_date（ yyyymmdd ） ww ） as weekn to_char（to_date（ yyyymmdd ） ww ） as week to_char（to_date（ yyyymmdd ） ww ） as week to_char（to_date（ yyyymmdd ） ww ） as weekn from dual

WEEK WEEK WEEK WEEK

Elapsed

asdb WF>

—— iw的算法为星期一至星期日算一周 且每年的第一个星期一为第一周

—— 例如 为星期六 所以用iw的算法是前年的 周 而 之后才是第一周的开始

—— 公式 每周第一天 next_day（date） + 周 *

—— 每周最后一天 next_day（date） + 周 *

—— 如果以iw格式为主 第 周的起迄如下

asdb WF>select next_day（to_date（ yyyymmdd ） MONDAY ）+ * as first_day next_day（to_date（ yyyymmdd ） MONDAY ）+ * as last_day from dual

FIRST_DAY LAST_DAY

JAN JAN

Elapsed

asdb WF>

asdb WF>select next_day（to_date（ yyyymmdd ） MONDAY ）+ * as first_day next_day（to_date（ yyyymmdd ） MONDAY ）+ * as last_day from dual

FIRST_DAY LAST_DAY

APR MAY

Elapsed

asdb WF>

—— 验证如下

asdb WF>select to_char（to_date（ yyyymmdd ） iw ） as weekn to_char（to_date（ yyyymmdd ） iw ） as week to_char（to_date（ yyyymmdd ） iw ） as week to_char（to_date（ yyyymmdd ） iw ） as weekn from dual

WEEK WEEK WEEK WEEK

Elapsed

其它

——== 查今天是 本月 的第几周

SELECT TO_CHAR（SYSDATE WW ） TO_CHAR（TRUNC（SYSDATE MM ） WW ） + AS weekOfMon from dual

或

SELECT TO_CHAR（SYSDATE W ） AS weekOfMon from dual

——== 查今天是 今年 的第几周

select to_char（sysdate ww ） from dual

或

select to_char（sysdate iw ） from dual

附注

上文所提之iw及ww格式在doc内解释如下

IW = Week of year （ or ） based on the ISO standard

WW = Week of year （ ） where week starts on the first day of the year and continues to the seventh day of the year

lishixinzhi/Article/program/Oracle/201311/18798

【二】SQL的基本函数

2.1 关系型数据库SQL命令类别

数据 *** 纵语言：DML: selectinsertdeleteupdatemerge.

数据定义语言：DDL: createalterdroptruncaterenamecomment.

事务控制语言：TCL: commitrollbacksavepoint.

数据控制语言：DCL: grantrevoke.

2.2 单行函数与多行函数

单行函数：指一行数据输入，返回一个值得函数。所以查询一个表时，对选择的每一行数据都会返回一个结果。

SQL>select empno,lower(ename) from emp

多行函数：指多行数据输入，返回一个值得函数。所以对表的群组进行 *** 作，并且每组返回一个结果。（典型的是聚合函数）

SQL>select sum(sal) from emp

2.3 单行函数的几种类型

2.3.1 字符型函数

lower('SQL Course')----->sql course 返回小写

upper('sql course')----->SQL COURSE 返回大学

initcap('SQL course')----->Sql Course 每个单字返回首字母大写

concat('good','string')---->good string 拼接 只能拼接2个字符串

substr('String',1,3)---->Str 从第1位开始截取3位数，

演变：只有两个参数的

substr('String',3) 正数第三位起始，得到后面所有字符

substr('String',-2) 倒数第二位，起始，得到最后所有字符

instr('t#i#m#r#a#n#','#') --->找第一个#字符在那个绝对位置，得到的数值

Instr参数经常作为substr的第二个参数值

演变：Instr参数可有四个之多

如select instr('aunfukk','u',-1,1) from dual倒数第一个u是哪个位置，结果返回5

length('String')---->6 长度，得到的是数值

length参数又经常作为substr的第三个参数

lpad('first',10,'#39)左填充

rpad(676768,10,'*')右填充

replace('JACK and JUE','J','BL')---->BLACK and BLUE

trim('m' from 'mmtimranm')---->timran 两头截，这里的‘m’是截取集，仅能有一个字符

trim( ' timran ')---->timran 作用是两头去空字符

处理字符串时，利用字符型函数的嵌套组合是非常有效的：

create table customers(cust_name varchar2(20))

insert into customers values('Lex De Hann')

insert into customers values('Renske Ladwig')

insert into customers values('Jose Manuel Urman')

insert into customers values('Joson Malin')

select * from customers

CUST_NAME

--------------------

Lex De Hann

Renske Ladwig

Jose Manuel Urman

Joson Malin

一共四条记录，客户有两个名的，也有三个名的，现在想列出仅有三个名的客户，且第一个名字用*号略去

答案之一：

SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') "CUST NAME"

FROM customers

WHERE INSTR(cust_name,' ',1,2)>0

CUST NAME

------------------------------------------------------------------------------------------------------------------------

*** De Hann

**** Manuel Urman

分析：

先用INSTR(cust_name,' ')找出第一个空格的位置，

然后，SUBSTR(cust_name,INSTR(cust_name,' '))从第一个空格开始往后截取字符串到末尾，结果是第一个空格以后所有的字符,

最后，LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*')用LPAD左填充到cust_name原来的长度，不足的部分用*填充，也就是将第一个空格前的位置，用*填充。

where后过滤是否有三个名字，INSTR(cust_name, ' ',1,2)从第一个位置，从左往右，查找第二次出现的空格，如果返回非0值，则说明有第二个空格，则有第三个名字。

2.3.2 数值型函数

round 对指定的值做四舍五入,round(p,s) s为正数时，表示小数点后要保留的位数，s也可以为负数，但意义不大。

round:按指定精度对十进制数四舍五入,如:round(45.923, 1),结果,45.9

round(45.923, 0),结果,46

round(45.923, -1),结果,50

trunc 对指定的值取整 trunc(p,s)

trunc:按指定精度截断十进制数,如:trunc(45.923, 1),结果,45.9

trunc(45.923),结果,45

trunc(45.923, -1),结果, 40

mod 返回除法后的余数

SQL>select mod(100,12) from dual

2.3.3 日期型函数

因为日期在oracle里是以数字形式存储的，所以可对它进行加减运算，计算是以天为单位。

缺省格式：DD-MON-RR.

可以表示日期范围：（公元前）4712 至（公元）9999

时间格式

SQL>select to_date('2003-11-04 00:00:00' ,'YYYY-MM-DD HH24:MI:SS') FROM dual

SQL>select sysdate+2 from dual当前时间+2day

SQL>select sysdate+2/24 from dual当前时间+2hour

SQL>select sysdate+2/1440 from dual当前时间+2分钟

SQL>select (sysdate-hiredate)/7 week from emp两个date类型差，结果是以天为整数位的实数。

①MONTHS_BETWEEN 计算两个日期之间的月数

SQL>select months_between('1994-04-01','1992-04-01') mm from dual

查找emp表中参加工作时间>30年的员工

SQL>select * from emp where months_between(sysdate,hiredate)/12>32

很容易认为单行函数返回的数据类型与函数类型一致，对于数值函数类型而言的确如此，但字符和日期函数可以返回任何数据类型的值。比如instr函数是字符型的，months_between函数是日期型的，但它们返回的都是数值。

②ADD_MONTHS 给日期增加月份

SQL>select hiredate,add_months(hiredate,4) from emp

③LAST_DAY 日期当前月份的最后一天

SQL>select hiredate,last_day(hiredate) from emp

④NEXT_DAY NEXT_DAY的第2个参数可以是数字1-7，分别表示周日--周六(考点）

比如要取下一个星期六，则应该是：

SQL>select next_day(sysdate,7) FROM DUAL

⑤ROUND(p,s)，TRUNC(p,s)在日期中的应用，如何舍入要看具体情况，s是MONTH按30天计，应该是15舍16入，s是YEAR则按6舍7入计算。

SQL>SELECT empno, hiredate,round(hiredate,'MONTH') AS round,trunc(hiredate,'MONTH') AS trunc FROM emp

SQL>SELECT empno, hiredate, round(hiredate,'YEAR') AS round,trunc(hiredate,'YEAR') AS trunc FROM emp

2.3.4 几个有用的函数和表达式

1）DECODE函数和CASE表达式：

实现sql语句中的条件判断语句，具有类似高级语言中的if-then语句的功能。

decode函数源自oracle, case表达式源自sql标准，实现功能类似，decode语法更简单些。

decode函数用法：

SQL>SELECT job, sal,

decode(job, 'ANALYST', SAL*1.1, 'CLERK', SAL*1.15,'MANAGER', SAL*1.20, SAL) SALARY FROM emp

decode函数的另几种常见用法：

SQL>select ename,job,decode(job,'MANAGER','中层干部') leader from emp

SQL>select ename,job,comm,decode (comm,null,'nonsale','sale') saleman from emp

注：单一列处理，共四个参数：含义是：comm 如果为null就取'nonsale，否则取'sale'

SQL>select sal,sign(sal-1500) from emp

SQL>select ename,decode (sign(sal-1500), 1, 'NORMAL','LOW') as "LEV" from emp

注：sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1，含义是：工资大于1500,返回1，真取'NORMAL'，假取'LOW'

CASE表达式第一种用法：

SQL>SELECT job, sal,

case job when 'ANALYST' then SAL*1.10

when 'CLERK' then SAL*1.15

when 'MANAGER' then SAL*1.20

else sal end SALARY

FROM emp

CASE表达式第二种用法：

SQL>SELECT job, sal, case

when job='ANALYST' then SAL*1.1

when job='CLERK' then SAL*1.15

when job='MANAGER' then SAL*1.20

else sal end SALARY

FROM emp

以上三种写法结果都是一样的

CASE第二种语法比第一种语法增加了搜索功能。形式上第一种when后跟定值，而第二种还可以使用表达式和比较符。

看一个例子

SQL>SELECT ename,sal,case

when sal>=3000 then '高级'

when sal>=2000 then '中级'

else '低级' end 级别

FROM emp

再看一个例子:使用了复杂的表达式

SQL>SELECT AVG(CASE

WHEN sal BETWEEN 500 AND 1000 AND JOB='CLERK'

THEN sal ELSE null END) "CLERK_SAL"

from emp

比较

SQL>select avg(sal) from emp where job='CLERK'

2）DISTINCT(去重)限定词的用法：

distinct貌似多行函数，严格来说它不是函数而是select子句中的一个选项。

SQL>select distinct job from emp消除表行重复值。

SQL>select distinct job,deptno from emp重复值是后面的字段组合起来考虑的

SQL>select distinct * from emp消除重复记录

3）sys_context 获取环境上下文的函数（多用于应用环境）

scott远程登录

SQL>select SYS_CONTEXT('USERENV','IP_ADDRESS') from dual

--------------------------------------------------------------------------------

192.168.0.136

SQL>select sys_context('userenv','sid') from dual

SYS_CONTEXT('USERENV','SID')

--------------------------------------------------------------------------------

129

SQL>select sys_context('userenv','terminal') from dual

SYS_CONTEXT('USERENV','TERMINAL')

--------------------------------------------------------------------------------

TIMRAN-222C75E5

the end ！！！

@jackman 共筑美好！

TO_DATE格式(以时间:2007-11-02 13:45:25为例)

Year:

yy two digits 两位年显示值:07

yyy three digits 三位年显示值:007

yyyy four digits 四位年显示值:2007

Month:

mmnumber 两位月 显示值:11

monabbreviated 字符集表示 显示值:11月,若是英文版,显示nov

month spelled out 字符集表示 显示值:11月,若是英文版,显示november

Day:

ddnumber 当月第几天显示值:02

dddnumber 当年第几天显示值:02

dyabbreviated 当周第几天简写显示值:星期五,若是英文版,显示fri

dayspelled out 当周第几天全写显示值:星期五,若是英文版,显示friday

ddspth spelled out, ordinal twelfth

Hour:

hhtwo digits 12小时进制显示值:01

hh24 two digits 24小时进制显示值:13

Minute:

mitwo digits 60进制显示值:45

Second:

sstwo digits 60进制显示值:25

其它

Q digit 季度 显示值:4

WWdigit 当年第几周显示值:44

Wdigit 当月第几周显示值:1

24小时格式下时间范围为： 0:00:00 - 23:59:59....

12小时格式下时间范围为： 1:00:00 - 12:59:59 ....

1. 日期和字符转换函数用法（to_date,to_char）

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual //日期转化为字符串

select to_char(sysdate,'yyyy') as nowYear from dual //获取时间的年

select to_char(sysdate,'mm')as nowMonth from dual //获取时间的月

select to_char(sysdate,'dd')as nowDayfrom dual //获取时间的日

select to_char(sysdate,'hh24') as nowHour from dual //获取时间的时

select to_char(sysdate,'mi')as nowMinute from dual //获取时间的分

select to_char(sysdate,'ss')as nowSecond from dual //获取时间的秒

select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss')from dual//

2.

select to_char( to_date(222,'J'),'Jsp') from dual

显示Two Hundred Twenty-Two

3.求某天是星期几

select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual

星期一

select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual

monday

设置日期语言

ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN'

也可以这样

TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')

4. 两个日期间的天数

select floor(sysdate - to_date('20020405','yyyymmdd')) from dual

5. 时间为null的用法

select id, active_date from table1

UNION

select 1, TO_DATE(null) from dual

注意要用TO_DATE(null)

6.月份差

a_date between to_date('20011201','yyyymmdd') and to_date('20011231','yyyymmdd')

那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。

所以，当时间需要精确的时候，觉得to_char还是必要的

7. 日期格式冲突问题

输入的格式要看你安装的ORACLE字符集的类型, 比如: US7ASCII, date格式的类型就是: '01-Jan-01'

alter system set NLS_DATE_LANGUAGE = American

alter session set NLS_DATE_LANGUAGE = American

或者在to_date中写

select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual

注意我这只是举了NLS_DATE_LANGUAGE，当然还有很多，

可查看

select * from nls_session_parameters

select * from V$NLS_PARAMETERS

8.

select count(*)

from ( select rownum-1 rnum

from all_objects

where rownum <= to_date('2002-02-28','yyyy-mm-dd') - to_date('2002-

02-01','yyyy-mm-dd')+1

)

where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' )

not in ( '1', '7' )

查找2002-02-28至2002-02-01间除星期一和七的天数

在前后分别调用DBMS_UTILITY.GET_TIME, 让后将结果相减(得到的是1/100秒, 而不是毫秒).

9. 查找月份

select months_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL

1

select months_between(to_date('02-01-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL

1.03225806451613

10. Next_day的用法

Next_day(date, day)

Monday-Sunday, for format code DAY

Mon-Sun, for format code DY

1-7, for format code D

11

select to_char(sysdate,'hh:mi:ss') TIME from all_objects

注意：第一条记录的TIME 与最后一行是一样的

可以建立一个函数来处理这个问题

create or replace function sys_date return date is

begin

return sysdate

end

select to_char(sys_date,'hh:mi:ss') from all_objects

12.获得小时数

extract()找出日期或间隔值的字段值

SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 2:38:40') from offer

SQL>select sysdate ,to_char(sysdate,'hh') from dual

SYSDATE TO_CHAR(SYSDATE,'HH')

-------------------- ---------------------

2003-10-13 19:35:21 07

SQL>select sysdate ,to_char(sysdate,'hh24') from dual

SYSDATE TO_CHAR(SYSDATE,'HH24')

-------------------- -----------------------

2003-10-13 19:35:21 19

13.年月日的处理

select older_date,

newer_date,

years,

months,

abs(

trunc(

newer_date-

add_months( older_date,years*12+months )

)

) days

from ( select

trunc(months_between( newer_date, older_date )/12) YEARS,

mod(trunc(months_between( newer_date, older_date )),12 ) MONTHS,

newer_date,

older_date

from (

select hiredate older_date, add_months(hiredate,rownum)+rownum newer_date

from emp

)

)

14.处理月份天数不定的办法

select to_char(add_months(last_day(sysdate) +1, -2), 'yyyymmdd'),last_day(sysdate) from dual

16.找出今年的天数

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

闰年的处理方法

to_char( last_day( to_date('02'| | :year,'mmyyyy') ), 'dd' )

如果是28就不是闰年

---