Oracle常用函数-日期时间类型
本文列出了以下8个Oracle常用的日期时间函数：
1、ADD_MONTHS
1.1 官方文档说明
ADD_MONTHS返回日期日期加上整数月份。一个月由会话参数NLS_CALENDAR定义。
date参数可以是一个datetime值或任何可以隐式转换为date的值。
integer参数可以是整数或任何可以隐式转换为整数的值。
无论日期的数据类型如何，返回类型始终为DATE。
如果date是该月的最后一天，或者结果月份的天数少于date的day部分，则结果为结果月份的最后一天。否则，结果具有与date相同的day部分。
1.2 语法
ADD_MONTHS(date, integer)
1.3 参数
date：被处理的目标日期，是日期时间类型，可以为（datetime值或任何可以隐式转换为date的值）
integer：目标日期将要加上的月份数，是整数类型，可以为（整数或任何可以隐式转换为整数的值）
1.4 返回结果
ADD_MONTHS的返回结果与目标日期的day部分有关：
a）一般情况：
生成日期为目标日期date加上月份数integer。
例如：ADD_MONTHS('2023-01-01',2) 结果为 '2023-03-01 00:00:00'，直接对 '2023-01-01' 加上2个月。
例如：ADD_MONTHS('2023-01-01',2) 结果为 '2023-03-01 00:00:00'，直接对 '2023-01-01' 加上2个月。
b）特殊情况：
day为目标日期当月的最后一天，生成日期的day部分为运算后该月的最后一天。
例如：ADD_MONTHS('2023-02-28',2) 结果为 '2023-04-30 00:00:00'，因为28号为2023年2月的最后一天，所以生成日期为2023年4月的最后一天。
day大于生成日期当月的总天数，生成日期的day部分为运算后该月的最后一天。
例如：ADD_MONTHS('2023-01-30',1) 结果为 '2023-02-28 00:00:00'，30号不是2023年1月的最后一天，但大于了2023年2月的总天数(28)，生成日期为2023年2月的最后一天。
1.5 范围探究
1.5.1 参数date 为 date 类型：
生成日期的时间部分默认为00:00:00
例如：ADD_MONTHS('2023-01-01',2) 结果为 '2023-03-01 00:00:00'。
1.5.2 参数date 为 datetime 类型：
生成日期的时间部分与目标日期相同
例如：ADD_MONTHS('2023-01-01 13:30:30',2) 结果为 '2023-03-01 13:30:30'。
1.5.3 参数integer为0：
返回datetime类型的目标日期
例如：ADD_MONTHS('2023-01-01',0) 结果为 '2023-01-01 00:00:00'。
1.5.4 参数integer为负整数：
返回目标日期date减去相应月份integer
例如：ADD_MONTHS('2023-01-01',-1) 结果为 '2022-12-01 00:00:00'。
1.5.5 参数integer为小数：
只是用integer整数部分参与运算
例如：ADD_MONTHS('2023-01-01',1.99) 与 ADD_MONTHS('2023-01-01',1.01) 结果同为 '2023-02-01 00:00:00'
2、EXTRACT
2.1 官方文档说明
EXTRACT从日期时间或间隔表达式中提取并返回指定日期时间字段的值。expr可以是任何计算为与请求字段兼容的日期时间或间隔数据类型的表达式:
如果请求YEAR或MONTH，则expr必须计算为数据类型DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONE、TIMESTAMP WITH LOCAL TIME ZONE或INTERVAL YEAR to MONTH的表达式。
如果请求DAY，则expr必须计算为数据类型为DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONE、TIMESTAMP WITH LOCAL TIME ZONE或INTERVAL DAY to SECOND的表达式。
如果请求HOUR, MINUTE或SECOND，则expr必须计算为数据类型TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE或INTERVAL DAY to SECOND的表达式。DATE在这里是无效的，因为Oracle数据库将其视为没有时间字段的ANSI DATE数据类型。
如果请求TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_ABBR, TIMEZONE_REGION或TIMEZONE_OFFSET，则expr必须计算为数据类型TIMESTAMP WITH TIMEZONE或TIMESTAMP WITH LOCAL TIMEZONE的表达式。
EXTRACT将expr解释为ANSI日期时间数据类型。例如，EXTRACT将DATE视为没有时间元素的ANSI DATE而不是旧的Oracle DATE。因此，只能从DATE值中提取YEAR、MONTH和DAY。同样，只能从TIMESTAMP WITH TIMEZONE数据类型中提取TIMEZONE_HOUR和TIMEZONE_MINUTE。
2.2 语法
EXTRACT(
{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND |
TIMEZONE_HOUR | TIMEZONE_MINUTE | TIMEZONE_REGION | TIMEZONE_ABBR
FROM { expr } )
2.3 参数
format FORM expr：可参考上方例举的官方文档。
2.4 返回结果
返回从expr中提取到的相应日期时间字段的值
EXTRACT(YEAR FROM DATE '2023-01-01') 和 EXTRACT(YEAR FROM TO_DATE('2023-01-01','YYYY-MM-DD')) 结果都为 2023
EXTRACT(MONTH FROM DATE '2023-01-01') 和 EXTRACT(MONTH FROM TO_DATE('2023-01-01','YYYY-MM-DD')) 和 EXTRACT(MONTH FROM (INTERVAL '1-1' YEAR TO MONTH)) 结果都为 1
EXTRACT(DAY FROM DATE '2023-01-01') 和 EXTRACT(DAY FROM TO_DATE('2023-01-01','YYYY-MM-DD')) 结果都为 1
EXTRACT(HOUR FROM TIMESTAMP '2023-01-01 13:30:00') 和 EXTRACT(HOUR FROM TO_TIMESTAMP('2023-01-01 13:30:00','YYYY-MM-DD HH24:MI:SS')) 结果都为 13
3、LAST_DAY
3.1 官方文档说明
LAST_DAY返回包含date的月份的最后一天的日期。
一个月的最后一天由会话参数NLS_CALENDAR定义。
无论日期的数据类型如何，返回类型始终为DATE。
3.2 语法
LAST_DAY(date)
3.3 参数
date：进行处理的目标日期，是日期时间类型
3.4 返回结果
包含date的月份的最后一天的日期
3.5 范围探究
3.5.1 参数date 为 date 类型：
生成日期的时间部分默认为00:00:00
例如：LAST_DAY('2023-01-01') 结果为 '2023-01-31 00:00:00'。
3.5.2 参数date 为 datetime 类型：
生成日期的时间部分与目标日期相同
例如：LAST_DAY('2023-01-01 13:30:30') 结果为 '2023-01-31 13:30:30'。
4、MONTHS_BETWEEN
4.1 官方文档说明
MONTHS_BETWEEN返回日期date1和日期date2之间的月数。
月份和月份的最后一天由参数NLS_CALENDAR定义。
如果date1晚于date2，则结果为正。
如果date1早于date2，则结果为负。
如果date1和date2是同一个月的同一天，或者都是同一个月的最后一天，那么结果总是一个整数。
否则，Oracle数据库根据31天的月份计算结果的小数部分，并考虑时间部分date1和date2的差异。
4.2 语法
MONTHS_BETWEEN(date1, date2)
4.3 参数
date1：日期1，是日期时间类型
date2：日期2，是日期时间类型
4.4 返回结果
4.4.1 date1早于date2，返回负数。
例如：MONTHS_BETWEEN('2023-01-01','2023-02-01') 结果为-1
4.4.2 date1晚于date2，返回正数。
例如：MONTHS_BETWEEN('2023-02-01','2023-01-01') 结果为 1
4.4.3 date1和date2是一个月的同一天，返回整数。
例如：MONTHS_BETWEEN('2023-02-01','2023-01-01') 结果为 1
4.4.4 date1和date2都是一个月的最后一天，返回整数。
例如：MONTHS_BETWEEN('2023-02-28','2023-01-31') 结果为 1
4.4.5 其他情况，返回小数。
例如：MONTHS_BETWEEN('2023-02-01','2023-01-02') 结果为 0.967741935
MONTHS_BETWEEN('2023-02-28','2023-01-02') 结果为 1.83870968
4.5 范围探究
MONTHS_BETWEEN将会考虑时间部分的差异，date的时间部分当作00:00:00
4.5.1 date1为datetime类型，date2为date类型：
例如：MONTHS_BETWEEN('2023-02-28 20:00:00','2023-01-02') 结果为 1.8655914
4.5.2 date1为date类型，date2为datetime类型：
例如：MONTHS_BETWEEN('2023-02-28','2023-01-02 20:00:00') 结果为 1.81182796
5、NEXT_DAY
5.1 官方文档说明
NEXT_DAY返回以字符命名的第一个工作日的日期，生成日期晚于当前日期。
无论日期的数据类型如何，返回类型始终为DATE。
参数char必须是会话的日期语言中的一周中的某一天，可以是全名或缩写。
所需的最小字母数是缩写版本中的字母数。将忽略有效缩写后面的任何字符。
返回值具有与参数日期相同的小时、分钟和秒组成部分。
5.2 语法
NEXT_DAY(date, char)
5.3 参数
date：进行处理的目标日期，是日期时间类型
char：字符命名的工作日，星期一到星期天
5.4 返回结果
返回具有与参数日期相同的小时、分钟和秒组成部分的日期类型。
5.4.1 工作日char在当前日期的工作日名称顺序之前，返回下周的日期
例如：当前日期是星期三，char值为星期二，则返回下周星期二的日期
NEXT_DAY('2023-01-04','Tuesday') 和 NEXT_DAY('2023-01-04','Tue') 结果都为 '2023-01-10 00:00:00'
5.4.2 工作日char与当前日期工作日名称相同，返回下周的日期
例如：当前日期是星期三，char值为星期三，则返回下周星期三的日期
NEXT_DAY('2023-01-04','Wednesday') 结果为 '2023-01-11 00:00:00'
5.4.3 工作日char在当前日期的工作日名称顺序之后，返回本周的日期
例如：当前日期是星期三，char值为星期四，则返回本周星期四的日期
NEXT_DAY('2023-01-04','Thursday') 结果为 '2023-01-05 00:00:00'
5.5 范围探究
5.5.1 参数date 为 date 类型：
返回日期的时间部分默认为00:00:00
例如： NEXT_DAY('2023-01-04','Thursday') 结果为 '2023-01-05 00:00:00'。
5.5.2 参数date 为 datetime 类型：
返回日期的时间部分与目标日期相同
例如： NEXT_DAY('2023-01-04 13:30:00','Thursday')结果为 '2023-01-05 13:30:30'。
6、ROUND
6.1 官方文档说明：
ROUND返回日期四舍五入到格式模型fmt指定的单位。
该函数对NLS_CALENDAR会话参数不敏感。它按照公历的规则运行。
返回的值总是数据类型为DATE，即使您为DATE指定了不同的datetime数据类型。
如果省略fmt，则日期四舍五入到最接近的一天。日期表达式必须解析为date值。
6.2 语法
ROUND(date [, fmt ])
6.3 参数
date：是日期时间类型
fmt：指定格式化模型
6.4 返回结果
返回被格式化后的日期时间类型
6.4.1 ROUND(sysdate,'year')，按月份四舍五入到最近一年的一月一号，0~6月取该日期年份
，7~
12月取该日期下一年份
例如：ROUND(TO_DATE('2023-06-30'),'year') 结果为 '2023-01-01 00:00:00'
ROUND(TO_DATE('2023-07-01'),'year') 结果为 '2024-01-01 00:00:00'
6.4.2 ROUND(sysdate,'month')，按天数四舍五入到最近一个月的一号，1~15天取该日期的当月月份，16~31天取该日期下一月份
例如：ROUND(TO_DATE('2023-01-15'),'month') 结果为 '2023-01-01 00:00:00'
ROUND(TO_DATE('2023-01-16'),'month') 结果为 '2023-02-01 00:00:00'
6.4.3 ROUND(sysdate,‘day’)，按工作日四舍五入到最近的星期天，星期一~星期三取前一个星期天
，
星期四~
星期天取后一个星期天
例如：ROUND(TO_DATE('2023-01-15'),'day') 结果为 '2023-01-15 00:00:00’ #2023-01-15 星期天
ROUND(TO_DATE('2023-01-18'),'day') 结果为 '2023-01-15 00:00:00’ #2023-01-18 星期三
ROUND(TO_DATE('2023-01-19'),'day') 结果为 '2023-01-22 00:00:00’ #2023-01-19 星期四
ROUND(TO_DATE('2023-01-22'),'day') 结果为 '2023-01-22 00:00:00’ #2023-01-22 星期天
6.5 范围探究
6.5.1 参数date 为 date 类型：
返回日期的时间部分默认为00:00:00
例如：ROUND(TO_DATE('2023-01-01'),'year') 结果为 '2023-01-01 00:00:00'。
6.5.2 参数date 为 datetime 类型：
存在fmt时时间部分会被舍去
例如：ROUND(TO_DATE('2023-01-01 13:30:30'),year) 结果为 '2023-01-01 00:00:00'。
不存在fmt时，时间部分舍入到最近的一天, 0~11小时入到当天，12
~
23小时入到下一天
例如：ROUND(TO_DATE('2023-01-01 11:59:59')) 结果为 ‘2023-01-01 00:00:00’
ROUND(TO_DATE('2023-01-01 12:00:00')) 结果为 ‘2023-01-02 00:00:00’
7、TO_CHAR
7.1 官方文档说明
TO_CHAR（datetime）将DATE、TIMESTAMP、TIMESTAMPWITH TIME ZONE、TIMESTAMP-WITH LOCAL TIME ZONED、interval DAY TO SECOND或interval YEAR TO MONTH数据类型的日期时间或间隔值转换为由日期格式fmt指定的格式的VARCHAR2数据类型的值。
如果省略fmt，则日期将转换为VARCHAR2值,如下所示：
DATE值将转换为默认日期格式的值。
TIMESTAMP和TIMESTAMP WITH LOCAL TIME ZONE值将转换为默认时间戳格式的值。
TIMESTAMP WITH TIME ZONE值将转换为具有时区格式的默认时间戳中的值。
间隔值将转换为间隔文字的数字表示形式。
7.2 语法
TO_CHAR({ datetime | interval } [, fmt [, ‘nlsparam’ ] ])
7.3 参数
datetime：是日期时间类型
fmt：指定格式化模型
7.4 返回结果
将日期时间类型转换为由日期格式fmt指定的格式的VARCHAR2类型
7.4.1 指定了fmt：
返回fmt格式指定的VARCHAR2类型，具体格式可见后附录表
7.4.2 未指定fmt：
DATE值将转换为默认日期格式的值。
TIMESTAMP和TIMESTAMP WITH LOCAL TIME ZONE值将转换为默认时间戳格式的值。
TIMESTAMP WITH TIME ZONE值将转换为具有时区格式的默认时间戳中的值。
间隔值将转换为间隔文字的数字表示形式。
8、TRUNC
8.1 官方文档说明
TRUNC（date）函数返回日期，其中一天中的时间部分被截断为格式模型fmt指定的单位。
此函数对NLS_CALENDAR会话参数不敏感。它按照公历的规则运行。
返回的值总是数据类型DATE，即使您为日期指定了不同的日期时间数据类型。
如果省略fmt，则使用默认格式模型“DD”，并且返回的值是日期截断为午夜时间的一天。
8.2 语法
TRUNC(date [, fmt ])
8.3 参数
date：是日期时间类型
fmt：指定格式化模型
8.4 返回结果
返回结果和fmt格式相关
8.4.1 YEAR，截取到该日期年份的第一月第一天的零时零分零秒
例如：TRUNC(TO_DATE('2023-02-02 13:30:30'),'YEAR') 结果为 '2023-01-01 00:00:00'
8.4.2 MONTH，截取到该日期月份的第一天的零时零分零秒
例如：TRUNC(TO_DATE('2023-02-02 13:30:30'),'MONTH') 结果为 '2023-02-01 00:00:00'
8.4.3 DD，截取到该日期当天的零时零分零秒
例如：TRUNC(TO_DATE('2023-02-02 13:30:30'),'DD') 结果为 '2023-02-02 00:00:00'
8.4.4 DAY，截取到该日期所在周的第一天的零时零分零秒，一周以星期天作为开始
例如：TRUNC(TO_DATE('2023-02-02 13:30:30'),'DAY') 结果为 '2023-01-29 00:00:00' #'2023-02-02' 星期四
8.4.5 HH，截取到该日期当天的小时数的零分零秒
例如：TRUNC(TO_DATE('2023-02-02 13:30:30'),'HH') 结果为 '2023-02-02 13:00:00'
8.4.6 MI，截取到该日期当天的分钟数的零秒
例如：TRUNC(TO_DATE('2023-02-02 13:30:30'),'MI') 结果为 '2023-02-02 13:30:00'
8.4.7 CC，截取到该日期年份取前两位+1的第一月第一天的零时零分零秒
例如：TRUNC(TO_DATE('2023-02-02 13:30:30'),'CC') 结果为 '2001-01-01 00:00:00'
8.4.8 Q，截取到该日期所在季度月份的第一天的零时零分零秒
例如：TRUNC(TO_DATE('2023-02-02 13:30:30'),'Q') 结果为 '2023-01-01 00:00:00'