文章目录
一、Note
1、日期时间
1.1、日期格式
1.1.1、日期模式
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mov4AJdW-1596386990037)(世纪转换rr-世纪的加减.png)]
-
YYYY
- 用四位数字表示年份
-
YY
- 用两位数字表示年份
- 不涉及世纪转换问题,以当前系统指定的世纪为准
-
RR
- 用两位数字表示年份
- 涉及世纪转换问题
-
RRRR
- 用四位数字表示年份
-
MM
- 用两位数字表示月份
- 取值范围是 1~12
-
MON
- 用三个字符表示月份
-
MONTH
- 用 英文单词 或 数字加中文 表示月份
-
DAY
- 表示一周中的第几天,全称
-
DY
- 表示一周中的第几天,缩写
-
D
- 用 一位数字 表示一周中的第几天,数字的有效范围是[1,7]
-
DD
- 用 两位数字表示 月份中的天数
-
DDD
- 用 三位数字 表示年份中的天数
-
HH
或HH12
- 表示以12小时计时表示的小时
- 取值范围是 1~12
-
HH24
- 表示以24小时计时表示的小时
- 取值范围是 0~23
-
am
/pm
- 上午/下午
-
MI
- 用 两位数字 表示分钟,[0,59]
-
SS
- 用 两位数字 表示秒数,[0,59],闰秒就是[0,60]
-
BC
或B.C.
- 表示公元前(Before Christ)
-
AD
或A.D.
- 表示公元后(Anno Domini)
1.1.2、设置格式
-
默认格式
- Oracle中默认的日期格式是
dd-mon-yy
- 英文环境下,
mon
表示用 月份名称的缩写(3个字符) - 中文环境下,
mon
表示 ‘X月’,比如 ‘9月’、‘12月’
- Oracle中默认的日期格式是
-
设置当前会话格式的日期格式
ALTER SESSION SET nls_date_format = '日期模式' ;
-
设置当前日期的字符集
alter session set nls_date_language='Simplified Chinese' ;
-
为某个指定的日期时间值设置格式
to_char( 日期时间值 , '日期模式' )
1.2、日期类型
-
DATE
-
内部采用 7字节 来存储日期时间
-
其中存储的7个属性
- 世纪
- 年份
- 月份
- 日期
- 小时
- 分钟
- 秒
-
-
TIMESTAMP
- 内部采用 7字节 或 11字节 来存储日期时间
- 高精度采用11字节来存储
TIMESTAMP[ (seconds_precision) ]
TIMESTAMP(9)
是精确到秒的后9位,默认是6位TIMESTAMP
类型的前7字节的存储与Date类型相同
-
date
和timestamp
的值是数字(字节拼起来是数字) -
对于日期而言一个单位表示一天
- 1/24表示一小时
- 1/24/60表示一分钟
- 1/24/60/60表示一秒
- …
-
DUMP
- 使用
dump()
函数可以查看日期类型数值的内部结构 - 结果中的
type
字段指的是日期类型的编号,编号相同即为同一日期类型
- 使用
2、日期函数
2.1、获取日期
-
sysdate
- 获取数据库系统的当前日期时间,sysdate函数不需要 ()
-
current_date
- 获取当前会话时区所对应的日期时间值
-
systimestamp
- 获得数据库系统中一个
timestamp with time zone
类型的日期时间值
- 获得数据库系统中一个
2.2、查询时区
-
dbtimezone
- 数据库时区
-
sessiontimezone
- 会话时区
2.3、日期转换
-
格式化
- 所谓日期格式化就是将7字节存储的日期时间值安装指定的模式转换为字符串形式
to_char( 日期时间值 , '日期模式' )
-
解析
- 将字符串按照指定模式解析为日期时间值
to_date( '字符串表示的日期时间' , '日期模式' )
-
世纪转换
-
YY格式
- 在以字符串表示的日期中,如果年份只有两位,采用YY格式解析时,该年份的世纪与数据库服务器上设置的当前世纪相同
-
RR格式
-
2.4、日期计算
-
last_day( date )
- 用于返回 指定日期 所在月份的最后一天 的对应日期
select last_day( '2016-01-01' ) from dual ;
-
next_day( date , c )
- 用于获取d之后第一个c对应的 日期
- 第一个参数d表示一个DATE值
- 第二个参数c是用字符串表示的 星期中的某一天,比如 ‘星期一’、‘MON’
-
add_months( date , n )
-
可以得到某一时间之前或者之后的n个月的对应日期
-
add_months(‘日期’ , Integer) ,Integer为变化月数
-
select add_months(‘2019/10/27’ , 2) ;
结果为 20191227 -
select to_char(add_months(to_date('1999-12-01','yyyy-mm-dd'),2),'yyyy-mm-dd') from dual;
结果为2000-02-01 -
注意
- The integer argument can be an integer or any value that can be implicitly converted to an integer. 如1.9999999999999是被直接截取成1了,Oracle所谓的implicitly convert是截掉小数点之后的部分
-
-
months_between( date1 , date2 )
- 用于返回 两个日期之间间隔的 月数,此数值是一个浮点数,计算的月数较精确
select months_between( '2016-10-07' , '2018-12-25' ) from dual ;
-
greatest( date1 , date2 )
- 比较日期,显示大的日期
select greatest( '01-JAN-16' , '15-MAY-16' ) from dual ;
2.5、日期值四舍五入
-
使用
round( date [, format] )
函数可以对DATE值进行四舍五入 -
参数d为日期类型,参数format为模式
-
若format为 year ,则表示四舍五入到年,以 7月1日00:00:00 为分界线
- 月份过半,年份就进位
-
若format为 month ,则表示四舍五入到月,以 每月的16日00:00:00 为分界线
- 月的天数过半,月份就进位
-
若format为 day ,则表示四舍五入到周,以 每周三中午12点:00:00 为分界线
- 国外以周日为每周起始
-
若format为 dd ,则表示四舍五入到日,以 每天中午12点:00:00 为分界线
-
超过分界线就入,未超过就舍
2.6、截断日期
-
使用
trunc( date [, format] )
可以将DATE值裁剪 -
若format为 year ,则表示截取到本年年初(即1月1日),超出部分全部舍弃
-
若format为 month ,则表示截取到本月初(即1日),超出部分全部舍弃
-
若format为 day,则表示截取到本周起点,超出部分全部舍弃
-
若format为 dd,则表示截取到当日起点,超出部分全部舍弃
-
2.7、注意
- 利用 trunc 和 round 也可以对 NUMBER 值的小数位进行截断和四舍五入
3、单行函数
3.1、单行数学函数
mod( m , n )
-power( m , n )
round( m [ , n ] )
trunc( m [ , n ] )
sqrt( x )
sign( n )
3.2、单行字符函数
-
length( characters )
-
lower( characters )
-
upper( characters )
-
concat( characters )
- 可以连接一个或者多个字符串,若其中一个为null,则返回null
3.3、单行转换函数
-
to_char( value [ , pattern ] )
NUMBER
/DATE
转为VARCHAR
select to_char(sysdate , 'DD-MONTH-YYYY' ) from dual;
-
to_date( chars [ , pattern ] )
CHAR
转为DATE
select to_date( '2016-10-07' , 'DD-MONTH-YYYY' ) from dual;
-
to_number( chars [ , pattern ] )
- 一般会自动把
chars
转换为number
- 一般会自动把
3.4、其他单行函数
-
nvl( frist , second )
- 如果first为空,则用second取代空
- 类似于Java中的 first==null?second:first
-
nvl2( first , second , third )
- 如果first不为空,则用second取代first,如果first为空,则用third取代空
- 类似于Java中的 first!=null?second:third
4、组函数
avg()
min()
max()
sum()
count()
5、instr()
-
解决模糊查询效率低下的问题
-
instr( str1 , str2 , [n,[m] )
:获取子串在原字符串中的位置 -
从str1中搜索str2,从n位置开始搜,m为子串str2第几次出现,默认为1
-
查询姓名包含A的员工信息
select * from emp where ename like '%A%' ;
select * from emp where instr( ename , 'A' , 1) > 0 ;
二、Code
1、日期时间
-
查看当前系统时间:
SELECT sysdate FROM dual ;
-
设置当前会话格式的日期格式
ALTER SESSION SET nls_date_format = 'ddd yyyy-mm-dd day hh24:mi:ss';
- 176 2020-06-24 星期三 16:30:04
ALTER SESSION SET nls_date_format = 'ddd yyyy-mm-dd dy hh24:mi:ss';
- 176 2020-06-24 星期三 16:30:30
-
为某个指定的日期时间值设置格式
-
SELECT to_char( sysdate , 'yyyy-mm-dd hh:mi:ss' ) from dual ;
- 2020-06-24 04:31:16
-
SELECT to_char( sysdate , 'yyyy-mm-dd am hh:mi:ss' ) from dual ;
- 2020-06-24 下午 04:31:39
-
SELECT to_char( sysdate , 'yyyy-mm-dd pm hh:mi:ss' ) from dual ;
- 2020-06-24 下午 04:31:46
-
SELECT to_char( sysdate , 'ad yyyy-mm-dd pm hh:mi:ss' ) from dual ;
- 公元 2020-06-24 下午 04:32:00
-
SELECT to_char( sysdate , 'bc yyyy-mm-dd pm hh:mi:ss' ) from dual ;
- 公元 2020-06-24 下午 04:32:07
-
SELECT to_char( sysdate , 'B.C. yy-mm-dd day ' ) FROM dual ;
(yy是当前世纪)- 公元 20-06-24 星期三
-
-
CHAR转为指定格式的DATE(当前会话的 nls_date_format 已经被设置过)
yy:不涉及世纪转换问题;rr:涉及世纪转换问题
SELECT to_date( '98-10-20' , 'yy-mm-dd' ) FROM dual ;
- 293 2098-10-20 星期一 00:00:00
SELECT to_date( '98-10-20' , 'rr-mm-dd' ) FROM dual ;
- 293 1998-10-20 星期二 00:00:00
-
使用dump()函数可以查看日期类型数值的内部结构
-
select dump( hiredate ) from emp where empno = 9257 ;
- Typ=12 Len=7: 119,185,1,23,1,1,1
-
select hiredate , dump( hiredate ) from emp where empno = 9257 ;
-
HIREDATE DUMP(HIREDATE) --------------------------------- -------------------------------- 023 1985-01-23 星期三 00:00:00 Typ=12 Len=7: 119,185,1,23,1,1,1
-
-
SELECT sysdate , dump( sysdate ) FROM dual ;
-
SYSDATE DUMP(SYSDATE) ---------------------- ---------------------- 176 2020-06-24 星期三 16:39:24 Typ=13 Len=8: 228,7,6,24,16,39,24,0
-
-
-
TIMESTAMP(9)是精确到秒的后9位,默认是6位;TIMESTAMP类型的前7字节的存储与Date类型相同
-
CREATE TABLE t_customers_logs ( id NUMBER(10) , operation VARCHAR2(10) , operation_time TIMESTAMP(9) ); -- 插入用户登录信息 INSERT INTO t_customers_logs VALUES ( 1 , '用户登录' , systimestamp ) ; commit ; SELECT * FROM t_customers_logs ; -- 结果 ID OPERATION OPERATION_TIME -------------------------------------------------- 1 用户登录 24-6月 -20 04.45.52.027000000 下午 SELECT dump( operation_time ) FROM t_customers_logs ; -- 结果 Typ=180 Len=11: 120,120,6,24,17,46,53,1,155,252,192 -- 插入用户退出信息 INSERT INTO t_customers_logs VALUES ( 2 , '用户退出' , sysdate ) ;
-
-
Oracle 数据库管理系统中 ,对于日期来说一个单位表示一天;sysdate不是DATE类型,它们的type(日期类型编号)不一样
SELECT sysdate FROM dual ;
->176 2020-06-24 星期三 16:50:08SELECT sysdate + 1 FROM dual ;
->177 2020-06-25 星期四 16:50:14SELECT sysdate + 6 FROM dual ;
->182 2020-06-30 星期二 16:50:23SELECT sysdate + 7 FROM dual ;
->183 2020-07-01 星期三 16:50:27SELECT sysdate + 1/24 FROM dual ;
->176 2020-06-24 星期三 17:50:43SELECT sysdate , sysdate + 1/24/60 FROM dual ;
->176 2020-06-24 星期三 16:51:12 ;176 2020-06-24 星期三 16:52:12SELECT sysdate , sysdate + 1/24/60/60 FROM dual ;
->176 2020-06-24 星期三 16:51:22 ;176 2020-06-24 星期三 16:51:23SELECT sysdate , sysdate + 1/24/60/60/1000 FROM dual ;
->176 2020-06-24 星期三 16:51:35 ;176 2020-06-24 星期三 16:51:35
-
dbtimezone:数据库时区
sessiontimezone:会话时区
-
SELECT dbtimezone , sessiontimezone FROM dual ;
-
DBTIME SESSIONTIMEZONE ------ --------------- +00:00 +08:00
-
-
2、世纪转换问题
-
yy:不涉及世纪转换问题,以当前系统指定的世纪为准,是当前世纪
-
查询系统时间
SELECT sysdate FROM dual ;
24-6月 -20
-
设置当前会话格式的日期格式:
ALTER SESSION SET nls_date_format = ‘日期模式’ ;
1、
ALTER SESSION SET nls_date_format = 'ddd yyyy-mm-dd day hh24:mi:ss' ;
176 2020-06-24 星期三 16:30:04
2、
ALTER SESSION SET nls_date_format = 'ddd yyyy-mm-dd dy hh24:mi:ss' ;
176 2020-06-24 星期三 16:30:30
-
为某个日期时间值设置格式
to_char( 日期时间值 , ‘日期模式’ ):NUMBER/DATE转为VARCHAR
1、
SELECT to_char( sysdate , 'yyyy-mm-dd hh:mi:ss' ) from dual ;
2020-06-24 04:31:16
2、
SELECT to_char( sysdate , 'yyyy-mm-dd am hh:mi:ss' ) from dual ;
2020-06-24 下午 04:31:39
3、
SELECT to_char( sysdate , 'yyyy-mm-dd pm hh:mi:ss' ) from dual ;
2020-06-24 下午 04:31:46
4、
SELECT to_char( sysdate , 'ad yyyy-mm-dd pm hh:mi:ss' ) from dual ;
公元 2020-06-24 下午 04:32:00
5、
SELECT to_char( sysdate , 'bc yyyy-mm-dd pm hh:mi:ss' ) from dual ;
公元 2020-06-24 下午 04:32:07
6、
SELECT to_char( sysdate , 'B.C. yy-mm-dd day ' ) FROM dual ;
公元 20-06-24 星期三
7、
SELECT to_char( sysdate , 'B.C. yyyy-mm-dd day ' ) FROM dual ;
公元 2020-06-24 星期三
-
to_date( chars [ , pattern ] ):CHAR转为DATE
1、
SELECT to_date( '98-10-20' , 'yy-mm-dd' ) FROM dual ;
293 2098-10-20 星期一 00:00:00
2、
SELECT to_date( '98-10-20' , 'rr-mm-dd' ) FROM dual ;
293 1998-10-20 星期二 00:00:00
-
使用dump()函数可以查看日期类型数值的内部结构
1、select dump( hiredate ) from emp where empno = 9257 ;
Typ=12 Len=7: 119,185,1,23,1,1,1
2、SELECT sysdate , dump( sysdate ) FROM dual ;
176 2020-06-24 星期三 16:50:08
Typ=13 Len=8: 228,7,6,24,16,39,24,0
- 对于日期而言一个单位表示一天
3、SELECT sysdate + 1 FROM dual ;
177 2020-06-25 星期四 16:50:14
4、SELECT sysdate + 6 FROM dual ;
182 2020-06-30 星期二 16:50:23
5、SELECT sysdate + 7 FROM dual ;
183 2020-07-01 星期三 16:50:27
6、SELECT sysdate + 1/24 FROM dual ;
176 2020-06-24 星期三 17:50:43
7、SELECT sysdate , sysdate + 1/24/60 FROM dual ;
176 2020-06-24 星期三 16:51:12
176 2020-06-24 星期三 16:52:12
8、SELECT sysdate , sysdate + 1/24/60/60 FROM dual ;
176 2020-06-24 星期三 16:51:22
176 2020-06-24 星期三 16:51:23
9、SELECT sysdate , sysdate + 1/24/60/60/1000 FROM dual ;
176 2020-06-24 星期三 16:51:35
176 2020-06-24 星期三 16:51:35
-
TIMESTAMP(9)是精确到秒的后9位,默认是6位,TIMESTAMP类型的前7字节的存储与Date类型相同
-- 建表 CREATE TABLE t_customers_logs ( id NUMBER(10) , operation VARCHAR2(10) , operation_time TIMESTAMP(9) ); -- 插入数据 -- systimestamp:获得数据库系统中一个 timestamp with time zone 类型的日期时间值 INSERT INTO t_customers_logs VALUES ( 1 , '用户登录' , systimestamp ) ; -- 查询 SELECT * FROM t_customers_logs ; -- 结果: 1 用户登录 24-6月 -20 04.45.52.027000000 下午 -- 查询日期类型数值operation_time的内部结构 SELECT dump( operation_time ) FROM t_customers_logs ; -- 结果: Typ=180 Len=11: 120,120,6,24,17,46,53,1,155,252,192 -- 插入数据 INSERT INTO t_customers_logs VALUES ( 2 , '用户退出' , sysdate ) ;
-
Oracle 数据库管理系统中 ,对于日期来说一个单位表示一天
-
sysdate不是DATE类型,它们的type(日期类型编号)不一样
-
数据库时区:dbtimezone
-
会话时区:sessiontimezone
-
SELECT dbtimezone , sessiontimezone FROM dual ;
+00:00 +08:00
3、使用YY/RR/YYYY格式处理入职日期
YYYY:用四位数字表示年份
YY:用两位数字表示年份,不涉及世纪转换问题,以当前系统指定的世纪为准
RR:用两位数字表示年份,涉及世纪转换问题
-
1、查询 emp 表中入职最晚的雇员信息
SELECT empno , ename , hiredate FROM emp WHERE hiredate = ( SELECT max(hiredate) FROM emp ) ;
-
2、查询 s_emp 表中入职最晚的雇员信息
SELECT id , first_name , start_date FROM s_emp ;
- 查询出的一条记录的start_date为:公元 2090-03-03 星期五 08:30:00
-
注意char转换date类型时的世纪
SELECT to_date( '04-05-06' , 'dd-mm-yyyy' ) FROM dual ;
- 公元 0006-05-04 星期二 00:00:00
SELECT to_date( '04-05-06' , 'dd-mm-yy' ) FROM dual ;
- 公元 2006-05-04 星期四 00:00:00
SELECT id , first_name , to_char( start_date , 'dd-mm-yy') FROM s_emp ;
- 此时查询出的start_date为:03-03-90
SELECT id , first_name , to_date( to_char( start_date , 'dd-mm-yy') , 'dd-mm-rr' ) FROM s_emp ;
- 此时查询出的start_date为:公元 1990-03-03 星期六 00:00:00
-
此时再次查询s_emp表中入职最晚的雇员信息
-
SELECT id , first_name , to_date( to_char( start_date , 'dd-mm-yy') , 'dd-mm-rr' ) FROM s_emp WHERE to_date( to_char( start_date , 'dd-mm-yy') , 'dd-mm-rr' ) = (SELECT max( to_date( to_char( start_date , 'dd-mm-yy') , 'dd-mm-rr' ) ) FROM s_emp ); -- 结果为 公元 1992-02-09 星期日 00:00:00
-
4、日期运算:last_day/next_day/add_months/months_between/round/trunc
-
SELECT sysdate FROM dual ;
:公元 2020-06-25 星期四 10:12:38 -
last_day( Date ):求 指定日期 所在月份的最后一天 的对应日期
SELECT last_day( sysdate ) FROM dual ;
- 公元 2020-06-30 星期二 10:07:53
SELECT last_day( to_date( '02-02-02' , 'dd-mm-yy' ) ) FROM dual ;
- 公元 2002-02-28 星期四 00:00:00
SELECT last_day( to_date( '02-02-04' , 'dd-mm-yy' ) ) FROM dual ;
- 公元 2004-02-29 星期日 00:00:00
-
next_day( Date , char ):获取指定日期以后第一个char对应的日期
1、求今天以后第一个 星期四
SELECT next_day( sysdate , '星期四' ) FROM dual ;
- 公元 2020-07-02 星期四 10:13:29
2、求 1919年 2月 最后一个 星期日
- 首先找到 1919年2月 最后一天
SELECT last_day( to_date( '1919-02-01' , 'yyyy-mm-dd' ) ) FROM dual ;
- 公元 1919-02-28 星期五 00:00:00
- 从 1919年2月 最后一天 向前 数 7 天
SELECT last_day( to_date( '1919-02-01' , 'yyyy-mm-dd' ) ) - 7 FROM dual ;
- 公元 1919-02-21 星期五 00:00:00
- 从 1919年2月 最后一天 向前 数 7 天后,再从这个时间点向后寻找 第一个 星期日 出现的位置
SELECT next_day(last_day(to_date('1919-02-01','yyyy-mm-dd'))-7,'星期日' FROM dual ;
- 公元 1919-02-23 星期日 00:00:00
3、求 2020年10月倒数第二个 星期六
SELECT next_day( last_day( to_date('2020-10-01','yyyy-mm-dd') )-14 ,'星期六' ) FROM dual ;
-
add_months( Date , Integer ):可以得到某一时间之前或者之后的n个月的对应日期
-
SELECT sysdate , add_months(sysdate , -10) , add_months( sysdate , 10) FROM dual ;
-
公元 2020-06-25 星期四 10:25:10
公元 2019-08-25 星期日 10:25:10
公元 2021-04-25 星期日 10:25:10
-
-
SELECT sysdate , add_months( sysdate , 1.5 ) FROM dual ;
-
Integer,若为浮点数,则截取小数点之前的数为Integer
-
公元 2020-06-25 星期四 10:26:12
公元 2020-07-25 星期六 10:26:12
-
-
-
months_between:用于返回 两个日期之间间隔的 月数,此数值是一个浮点数,计算的月数较精确
SELECT months_between( to_date( '99-10-10' , 'rr-mm-dd' ) , to_date( '20-06-25' , 'rr-mm-dd' ) ) FROM dual ;
- -248.48387
SELECT months_between( to_date( '20-06-25' , 'rr-mm-dd' ) , to_date( '99-10-10' , 'rr-mm-dd' ) ) FROM dual ;
- 248.483871
-
round( date [, format] ) :日期值四舍五入,过半就"进位",未超过就舍
SELECT to_date( '2019-05-10 11:00:00' , 'yyyy-mm-dd hh24:mi:ss') FROM dual ;
- 公元 2019-05-10 星期五 11:00:00
1、若format为 year ,则表示四舍五入到年,以 7月1日00:00:00 为分界线
SELECT round( to_date( '2019-05-10 11:00:00' , 'yyyy-mm-dd hh24:mi:ss') , 'year' ) FROM dual ;
- 公元 2019-01-01 星期二 00:00:00
ELECT round( to_date( '2019-07-10 11:00:00' , 'yyyy-mm-dd hh24:mi:ss') , 'year' ) FROM dual ;
- 公元 2020-01-01 星期三 00:00:00
SELECT round( to_date( '2019-07-01 00:00:00' , 'yyyy-mm-dd hh24:mi:ss') , 'year' ) FROM dual ;
- 公元 2020-01-01 星期三 00:00:00
2、若format为 month ,则表示四舍五入到月,以 每月的16日00:00:00 为分界线
SELECT round( to_date( '2019-05-10 23:59:59' , 'yyyy-mm-dd hh24:mi:ss') , 'month' ) FROM dual ;
- 公元 2019-05-01 星期三 00:00:00
SELECT round( to_date( '2019-02-15 23:59:59' , 'yyyy-mm-dd hh24:mi:ss') , 'month' ) FROM dual ;
- 公元 2019-02-01 星期五 00:00:00
SELECT round( to_date( '2019-05-16 00:00:00' , 'yyyy-mm-dd hh24:mi:ss') , 'month' ) FROM dual ;
- 公元 2019-06-01 星期六 00:00:00
3、若format为 dd ,则表示四舍五入到日,以 每天中午12点:00:00 为分界线
SELECT round( to_date( '2019-02-15 23:59:59' , 'yyyy-mm-dd hh24:mi:ss') , 'dd' ) FROM dual ;
- 公元 2019-02-16 星期六 00:00:00
SELECT round( to_date( '2019-02-15 12:00:00' , 'yyyy-mm-dd hh24:mi:ss') , 'dd' ) FROM dual ;
- 公元 2019-02-16 星期六 00:00:00
SELECT round( to_date( '2019-02-11 11:59:59' , 'yyyy-mm-dd hh24:mi:ss') , 'dd' ) FROM dual ;
- 公元 2019-02-11 星期一 00:00:00
4、若format为 day ,则表示四舍五入到周,以 每周三中午12点:00:00 为分界线(国外以周日为每周起始)
-
SELECT round( to_date( '2019-02-11 11:59:59' , 'yyyy-mm-dd hh24:mi:ss') , 'day' ) FROM dual ;
- 公元 2019-02-10 星期日 00:00:00
-
SELECT round( to_date( '2020-06-24 11:59:59' , 'yyyy-mm-dd hh24:mi:ss') , 'day' ) FROM dual ;
- 公元 2020-06-21 星期日 00:00:00
-
SELECT round( to_date( '2020-06-24 12:00:00' , 'yyyy-mm-dd hh24:mi:ss') , 'day' ) FROM dual ;
- 公元 2020-06-28 星期日 00:00:00
-
使用 trunc( date [, format] )可以将DATE值裁剪
- 若format为 year ,则表示截取到本年年初(即1月1日),超出部分全部舍弃
- 若format为 month ,则表示截取到本月初(即1日),超出部分全部舍弃
- 若format为 day,则表示截取到本周起点,超出部分全部舍弃
- 若format为 dd,则表示截取到当日起点,超出部分全部舍弃
SELECT trunc( to_date( '2020-06-24 12:00:00' , 'yyyy-mm-dd hh24:mi:ss') , 'day' ) FROM dual ;
- 公元 2020-06-21 星期日 00:00:00
SELECT trunc( to_date( '2020-06-27 12:00:00' , 'yyyy-mm-dd hh24:mi:ss') , 'day' ) FROM dual ;
- 公元 2020-06-21 星期日 00:00:00
SELECT trunc( to_date( '2020-06-28 12:00:00' , 'yyyy-mm-dd hh24:mi:ss') , 'day' ) FROM dual ;
- 公元 2020-06-28 星期日 00:00:00
-
利用 trunc 和 round 也可以处理 NUMBER 值
1、round,按Integer值对NUMBER的小数位进行四舍五入
-
SELECT round( 3.1415926 , 3 ) FROM dual ;
- 3.142
-
SELECT round( 3.1415926 , 2 ) FROM dual ;
- 3.14
-
SELECT round( 3.1415926 , 0 ) FROM dual ;
- 3
2、trunc,按Integer值对NUMBER的小数位进行截断,注意不会五入
SELECT trunc( 3.1415926 , 3 ) FROM dual ;
- 3.141
SELECT trunc( 3.1415926 , 2 ) FROM dual ;
- 3.14
SELECT trunc( 3.1415926 , 0 ) FROM dual ;
- 3
-
5、其他函数:to_number/nvl2
-
SELECT 100 + 100 FROM dual ;
- 200
-
隐式数据类型转换: ‘100’ --> 100
SELECT '100' + 100 FROM dual ;
- 200
SELECT '100' + '100' FROM dual ;
- 200
SELECT '100' - '100' FROM dual ;
- 0
-
to_number( chars [ , pattern ] ):一般会自动把chars转换为number
SELECT to_number('100') + to_number('100') FROM dual ;
- 200
- ``
-
nvl( frist , second ):类似于Java中的 first==null?second:first
-
nvl2( first , second , third ):类似于Java中的 first!=null?second:third
SELECT empno , ename , job , sal , NVL2( comm , '有提成' , '没有提成' ) AS comm FROM emp ;