Oracle 基础总结:日期函数专题
开发工具与关键技术: oracle plsql developer
撰写时间:2019-03-30
日期函数:
-
(一)日期的数学运算注意事项:
- (1) 在日期上加上或减去一个数字结果仍为日期
- (2) 两个日期相减返回日期之间相差的天数。
- (3) 日期不允许做加法运算,无意义
- (4) 可以用数字除24来向日期中加上或减去天数 (二)参数:不区分大小写,
- 年 :yyyy 、yy、YEAR
- 月 :mm、MONTH
-
星期:DAY
日 :dd
时 :hh
分 :mi
秒 :ss
1、SYSDATE
返回系统的当前日期和时间:包括年、月、日、时、分、秒。
查询语句例句:
select sysdate from dual; -- 2019/3/30 21:57:34
查询结果视图:
2、日期函数:
MONTHS_BETWEEN()、 ADD_MONTHS()、 NEXT_DAY()、 LAST_DAY()
查询语句例句:
select
MONTHS_BETWEEN ('01-9月-1993','11-1月-1994') as MONTHS_BETWEEN ,
ADD_MONTHS ('11-1月-94',25) as ADD_MONTHS ,
NEXT_DAY ('01-1月-95','星期五') as NEXT_DAY ,
LAST_DAY('01-2月-95') as LAST_DAY
from dual;
查询结果视图:
(1) MONTHS_BETWEEN(日期1,日期2):返回两个日期相差的月数。
计算方式是日期1减去日期2,然后转换成月数,如果日期1>日期2,值为正;反之,值为负。
(2) ADD_MONTHS(日期,数值):向指定日期中加上若干月数。
当前月份+数值=增加后的月份,
如果增加后的月份>12,则计算 (增加后的月份 - 12)/12= 商 + 余数,
当余数 > 0, 日期的年份 当前年份 + 商,月份变为余数;
当余数 = 0, 日期的年份变为 当前年份 + 商 - 1,月份为12月。
如果增加后的月份<12,则年份不变,月份变为增加后的月份。
(3) NEXT_DAY(日期,星期几):返回值为指定日期的下一个星期几。
参数的星期几使用汉字或英文是由使用的工具的语言而定的。
(4)LAST_DAY(日期):返回值为指定日期中的月份的最后一天。
3、日期函数: ROUND(日期,参数):日期的四舍五入。
查询语句例句:
select SYSDATE as 当前时间, --2019/3/30 10:40:41
ROUND(SYSDATE, 'YEAR') as 年,-- 四舍五入到 年 YEAR :不分大小写,也可以是 yyyy 或 yy
ROUND(SYSDATE, 'MONTH') as 月,-- 四舍五入到 月 MONTH:不分大小写,也可以是 mm
ROUND(SYSDATE, 'DAY') as 星期,-- 四舍五入到 星期 DAY:不分大小写
ROUND(SYSDATE, 'dd') as 日, -- 四舍五入到 日 DD:不分大小写
ROUND(SYSDATE, 'hh') as 时, -- 四舍五入到 时 HH:不分大小写
ROUND(SYSDATE, 'mi') as 分 -- 四舍五入到 分 MI:不分大小写
from dual;
查询结果视图:
个人理解:
对于日期的四舍五入,它和数学的四舍五入不太一样。
对于月来说是‘6舍7入’,当月份>=7,就往前进1,即年份+1,否则,月被舍去,归1,年份不变;
对于日来说是‘16舍17入’,当日>=17,就往前进1,即月份+1,否则,日被舍去,归1,月份不变。
对于时来说是‘11舍12入’,当日>=12,就往前进1,即日+1,否则,时被舍去,归0,不显示,日不变 。(24小时制)
对于分来说是‘29舍30入’,当分>=30,就往前进1,即时+1,否则,分被舍去,归0。
对于秒来说是‘29舍30入’,当秒>=30,就往前进1,即分+1,否则,秒被舍去,归0。
对于星期来说是‘三舍四入’,当指定日期的星期部分大于或等于星期四,则星期+1,即返回下星期的星期天的日期;否则,返回该星期的最小值 星期天的日期。 (星期从星期日数起)
如果参数为’YEAR’,即是日期四舍五入到年,判断月份,月份>=6,年份+1,否则年份不变,月和日的值为最小值的1月1日。时间部分被去掉。
如果参数为’MONTH’,即是日期四舍五入到月,判断日,日>=17,月份+1,否则月份不变,日的值为1。
如果 月份+1>12,则年份+1,月和日的值为最小值的1月1日。时间部分被去掉。
如果参数为DAY,即是日期四舍五入到星期,判断当前日期的星期部分是否大于或等于星期四,如果是,则星期+1,即返回下星期的星期天的日期,如果否,返回该星期的最小值 星期天的日期。时间部分被去掉。
(星期从星期日数起)
如果参数为’DD’,即是日期四舍五入到天,判断时,时>=12,日+1,否则日不变。
如果 日+1>当前月的最后一日,则月份+1,日的值为最小值1日;
如果 月份+1>12,则年份+1,月和日的值为最小值的1月1日。时间部分会被去掉。
如果参数为’HH’,即是日期四舍五入到时,判断分,分>=30,时+1,否则时不变,分的值为0。
如果 时+1>=24,则日+1。
如果 日+1>当前月的最后一日,则月份+1,日的值为最小值1日;
如果 月份+1>12,则年份+1,月和日的值为最小值的1月1日。
如果参数为’MI’,即是日期四舍五入到分,判断秒,秒>=30,分+1,否则分不变,秒的值为0。
如果 分+1>=60,则时+1,否则分不变。
如果 时+1>=24,则日+1,否则时不变。
如果 日+1>当前月的最后一日,则月份+1,日的值为最小值1日,否则日不变;
如果 月份+1>12,则年份+1,月和日的值为最小值的1月1日,否则月不变。
再次注意:只有当参数为’HH’或’MI’时,时间部分才不会被去掉。
4、日期函数: TRUNC (日期,参数):日期的截断。
查询语句例句:
select SYSDATE as 当前时间, --2019/3/30 21:02:24
TRUNC(SYSDATE,'YEAR') as 年, -- 截断到 年
TRUNC(SYSDATE,'MONTH') as 月, -- 截断到 月
TRUNC(SYSDATE,'DAY') as 星期,-- 截断到 星期
TRUNC(SYSDATE,'dd') as 日, -- 截断到 日
TRUNC(SYSDATE,'hh') as 时, -- 截断到 时
TRUNC(SYSDATE,'mi') as 分 -- 截断到 分
from dual;
查询结果视图:
个人理解:
日期的截断, 是对当前的日期的部分值还原为最小值
如果参数为’YEAR’,即日期截断到年,年的值不变,月和日的值为最小值的1月1日。时间部分被去掉。
如果参数为’MONTH’,即日期截断到月,年、月的值不变,日的值为最小值的1日。时间部分被去掉。
如果参数为DAY,即日期截断到星期,返回的值为当前星期的最小值 星期日的日期。(星期从星期日数起)时间部分被去掉。
如果参数为DD,即日期截断到日,年、月、日的值都不变,时间部分被去掉。
如果参数为’HH’,即日期截断到日,年、月、日、时的值都不变,分和秒的值都为0。
如果参数为MI,即日期截断到日,年、月、日、时、分的值都不变,秒的值都为0。
再次注意:只有当参数为’HH’或’MI’时,时间部分才不会被去掉。
5、返回日期的指定部分: EXTRACT(指定部分 from 日期)
查询语句例句:
select sysdate as "当前时间", --2019/3/30 21:33:55
extract(year from sysdate) AS "年" , --返回日期的 年 部分
extract(month from sysdate) AS "月" , --返回日期的 月 部分
extract(day from sysdate) AS "日" --返回日期的 日 部分
from dual;
查询结果视图:
更多关于日期提取函数的内容,如 提取时分秒 等,在下面的链接中。
链接: oracle中extract()函数----用于截取年、月、日、时、分、秒.
作者:小强斋太。
6、TO_CHAR函数对日期的转换
查询语句例句:
select systimestamp ,-- 30-3月 -19 09.52.45.024000 下午 +08:00
to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ss.ff3 ') , --2019-03-30 21:51:25.765
TO_CHAR(sysdate,'yyyy-mm-dd hh:mi:ss'),-- 2019-03-30 09:51:25
from dual;
1)systimestamp:查询系统时间,查询结果包括:年、月、日、时、分、秒、毫秒、上下午、时区。
而sysdate的返回结果只包括:年、月、日、时、分、秒。
2)TO_CHAR(日期,日期格式):将日期转化成指定格式的字符串。
3)TO_CHAR函数在where子句的使用
Select employee_id, last_name, hire_date from employees
where to_char(hire_date,'yyyy-mm-dd') = '1987-09-17';
查询结果视图:
注意日期格式:
(1)日期格式必须包含在单引号中而且大小写敏感。
(2)日期格式可以包含任意的有效的日期格式。
(3)日期之间用逗号隔开。
7、TO_DATE 函数对字符的转换:使用 TO_DATE 函数将字符转换成数字
查询语句例句:
SELECT TO_DATE('2019年03月29日 08:10:21','yyyy"年"mm"月"dd"日"hh:mi:ss')
From dual;
查询结果视图:
个人理解:
左边的字符串,对应着右边符合的日期格式,可以获取到左边的日期的数字部分,然后使用获取到的数字以其默认显示的日期格式显示结果。
以上内容部分来源于老师的教学,部分是个人的理解,难免有些理解得不是很到位。
如果有不正确的地方,还请指正。
感谢您的浏览。