Oracle PL/SQL——日期时间函数的运用

ChlinRei

已于 2022-12-21 16:18:31 修改

阅读量5.2k

点赞数 3

分类专栏： Oracle 文章标签： sql 数据库 oracle

于 2022-12-15 08:00:00 首次发布

本文链接：https://blog.csdn.net/mr_ren_0_1/article/details/128323610

版权

Oracle 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

建表、数据插入

CREATE TABLE TEST.STU2(
	SNO VARCHAR2(10) PRIMARY KEY,
	SNAME VARCHAR2(10),
	SGENTLE VARCHAR2(2),
	SAGE NUMBER(2),
	SBIRTH DATE,
	SDEPT VARCHAR2(20));
	INSERT INTO TEST.STU2 VALUES('0001','张三','男',18,to_date('2000-01-11','yyyy-mm-dd'),'计算机');
	INSERT INTO TEST.STU2 VALUES('0002','李四','男',19,'12-6月-00','艺术');

查看表结构

DESC test.STU2;
名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 SNO                                       NOT NULL VARCHAR2(10)
 SNAME                                              VARCHAR2(10)
 SGENTLE                                            VARCHAR2(2)
 SAGE                                               NUMBER(2)
 SBIRTH                                             DATE
 SDEPT                                              VARCHAR2(20)

1.日期运算

将STU2表中所有学生的出生日期都推后一年

SELECT SNO,SNAME,ADD_MONTHS(SBIRTH,12)
FROM test.stu2

ADD_MONTHS(d,n)函数，其中d表示日期，n表示要加上的月数。如果n设置为负数，就能实现月份的算术减运算。

注意：ADD_MONTHS()函数的参数n应当是整数，给出小数时，整数被截为小于该数的最大整数，负数被截为大于该数的最小整数。

2.日期格式化输出

时间默认格式为“日-月-年”，如“12-6月-00”表示的是2000年6月12日。

本案例将当前日期和时间以多种格式输出。

SELECT to_char(SYSDATE,'YYYY/MM/DD')
FROM DUAL;
SELECT to_char(SYSDATE,'YYYY/MM/DD HH:MI:SS')
FROM DUAL;
SELECT to_char(SYSDATE,'YYYY/MM/DD HH24:MI:SS')
FROM DUAL;

TO_CHAR(d|n[,fmt])函数是将日期和数字转换为指定格式字符串函数，其中，参数d表示日期型数据，参数n表示数值型数据，参数fmt是格式化字符串

格式字符串	含义
D	星期（日=1、一=2、二=3、…）
DDD	一年之第几天
WW	一年之第几周
W	一月之第几周

自定义格式输出：

SELECT to_char(SYSDATE,'YYYY"年"MM"月"DD"日"')
FROM DUAL;

注意：自定义格式输出时，在格式化字符串中，使用双引号对非格式化字符进行引用。

3.提取日期特定部分

输出当前系统日期时间，并分别将其年份、月份、日、小时、分、秒提取出来，以字符的形式显示

SELECT SYSDATE 当前日期时间,
Extract(YEAR FROM SYSDATE) 年份,
Extract(MONTH FROM SYSDATE) 月份,
Extract(DAY FROM SYSDATE) 日,
Extract(HOUR FROM Systimestamp) 小时,
Extract(MINUTE FROM Systimestamp) 分,
Extract(SECOND FROM Systimestamp) 秒
FROM dual

EXTRACT(fmt from d)函数实现特定日期时间的提取。参数fmt有YEAR、MONTH、DAY、HOUR、MINUTE、SECOND 六种。YEAR、MONTH、DAY可以为DATE类型匹配，也可以与TIMESTAMP类型匹配；但是HOUR、MINUTE、SECOND必须与TIMESTAMP类型匹配。

此外，ROUND(d[,fmt])函数也可以实现部分日期的提取和运算。该函数返回一个以fmt为格式的四舍五入日期值，d是日期，fmt是格式模型，默认fmt为DDD，即月中第几天。

注意：在使用EXTRACT函数提取当前系统时间的小时值的时候，HOUR匹配的结果中没有加上时区，因此在中国运行的结果小8小时。

4.数据类型转化

将一串数字以科学计数法表示，并在前面加上一个本地货币符号输出，再一个字符串以数字形式输出。

SELECT to_char(-123456.78,'L9.9EEEEPR') 科学计数法
FROM DUAL--  <￥1.2E+05>
SELECT to_number('-$12,345.67','$99,999.99') 数字
FROM DUAL --  -12345.67

to_char函数实现对数字类型转换为字符串类型

to_number函数实现对字符串类型转换为数字类型

to_date函数实现将字符串转换为日期类型

5.提取日期的星期值

将具体日期的星期值提取出来，返回当前系统日期的星期值。

SELECT to_char(SYSDATE,'DAY')
FROM DUAL-- 星期三
SELECT to_char(SYSDATE,'D')
FROM DUAL-- 4

使用参数D，输出指定日期是星期几中的那一天，返回的结果是以数字表示的星期值。

注意：以数字表示星期值时，数字取值（1~7），分别表示从星期日到星期六的7天，即对应星期日，以此类推，4对应星期三。

6.提取一年中的第几天

SELECT to_char(SYSDATE,'DDD')
FROM DUAL

将to_char函数中的目标日期进行修改，可以获取任何一天在该年中是第几天。

SELECT to_char(to_date('2022-12-12','YYYY-MM-DD'),'DDD')
FROM DUAL

7.计算时间差

计算指定两个日期之间相差的天数，当前系统日期与“2022-01-01”之间相差多少天。

SELECT FLOOR(to_number(SYSDATE-to_date('2022-01-01','YYYY-MM-DD'))) AS SPANDAYS
FROM DUAL

FLOOR函数取小于等于相差值的最大整数。

计算系统当前日期与指定日期的时间差直接使用了算术运算符“-”。

计算系统当前日期与“2022-01-01”相差多少个月。

SELECT CEIL(MONTHS_BETWEEN(SYSDATE,to_date('2022-01-01','YYYY-MM-DD'))) AS SPANDAYS
FROM DUAL

MONTHS_BETWEEN函数代替算术运算符“-”完成两个日期的减法运算，该函数直接返回两个日期相差的月份。

获取两个日期相差的年份。

SELECT FLOOR(to_number(SYSDATE-to_date('2022-01-01','YYYY-MM-DD'))/365) AS SPANDAYS
FROM DUAL

注意：计算两个时间相差的小时数，需要在现实相差天数的SQL语句中乘以24；计算两个时间相差的分钟数，只要在现实相差天数的SQL语句中乘以“24*60”，依次类推也可以计算两个时间相差的秒数。

8.获取某月的第一天与最后一天

获取系统当前日期的当月第一天和最后一天的日期，以及上一个月的第一天与最后一天的具体日期

SELECT TRUNC(TRUNC(SYSDATE, 'MONTH') -1, 'MONTH') FIRST_DAY_LAST_MONTH,
TRUNC(SYSDATE, 'MONTH') -1 / 86400 LAST_DAY_LAST_MONTH,
TRUNC(SYSDATE, 'MONTH') FIRST_DAY_CUR_MONTH,
LAST_DAY(TRUNC(SYSDATE, 'MONTH')) + 1-1 / 86400 LAST_DAY_CUR_MONTH
FROM DUAL