SQL日期函数

目录

引言：

一：获取日期时间函数

1.1获取当前时间

1.2获取当前日期

二：日期格式化

2.1日期转指定格式字符串

2.2 字符串转日期

三：日期间隔

3.1增加日期间隔

 3.2减去一个时间间隔

3.3日期相差天数

3.4相差时间（小时）

四：星期操作

五：其他操作

 六：总结

---

引言：

在 SQL 中，日期函数具有多种重要作用：

 

1. 数据筛选和查询：

 

• 可以根据特定的日期范围来获取所需的数据。例如，通过 BETWEEN 操作符和日期函数，筛选出在某个时间段内的订单记录。
• 能够提取日期中的特定部分，如年、月、日、小时等，以便按照这些部分进行分类和统计。

 

2. 数据处理和转换：

 

• 将日期格式进行转换，以适应不同的显示需求或与其他系统进行数据交互。
• 计算日期之间的差值，比如计算两个日期之间的天数、月数或年数。

 

3. 数据验证和约束：

 

• 在插入或更新数据时，使用日期函数来验证输入的日期是否合法。

 

4. 数据排序：

 

• 按照日期字段对数据进行升序或降序排列，以便直观地查看数据的时间顺序。

 

例如，在 MySQL 中，使用 DATE_FORMAT() 函数可以将日期格式化为特定的字符串格式，如 SELECT DATE_FORMAT('2024-07-15', '%Y-%m-%d') 会返回 2024-07-15 。

 

又比如，通过 DATEDIFF() 函数计算两个日期之间的天数差，如 SELECT DATEDIFF('2024-07-15', '2024-07-01') 会返回 14 。

 

总之，SQL 中的日期函数为处理和操作与日期相关的数据提供了强大而灵活的工具，使得数据处理更加准确和高效。

接下来就为大家介绍几种在SQL中常用的日期函数： 

一：获取日期时间函数

1.1获取当前时间

SELECT NOW(); # 2024-07-15 21:28:45

1.2获取当前日期

SELECT CURRENT_DATE(); # 2024-07-15

1.3 获取当前时间

SELECT CURRENT_TIME();

二：日期格式化

2.1日期转指定格式字符串

SELECT DATE_FORMAT('2024-07-15 21:31:01', '%Y%m%d%H%i%s');# 20210715213101
说明：
%W 星期名字(Sunday……Saturday) 
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。)
%Y 年, 数字, 4 位 ★★★
%y 年, 数字, 2 位
%a 缩写的星期名字(Sun……Sat)
%d 月份中的天数, 数字(00……31) ★★★
%e 月份中的天数, 数字(0……31)
%m 月, 数字(01……12) ★★★ month
%c 月, 数字(1……12)
%b 缩写的月份名字(Jan……Dec)
%j 一年中的天数(001……366)
%H 小时(00……23)★★★
%k 小时(0……23)
%h 小时(01……12) 
%I 小时(01……12)
%l 小时(1……12)
%i 分钟, 数字(00……59) ★★★ minite
%r 时间,12 小时(hh:mm:ss [AP]M)
%T 时间,24 小时(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59) ★★★
%p AM或PM
%w 一个星期中的天数(0=Sunday ……6=Saturday )
%U 星期(0……52), 这里星期天是星期的第一天,查询指定日期属于当前年份的第几个周 ★★★★
%u 星期(0……52), 这里星期一是星期的第一天

实例如下：

# 日期格式化
select date_format(now(),'%Y%m%d%H%i%s');
# 获取当前是星期几
select date_format(now(),'%Y%m%W');
# 查看当前属于一年中的第几个周 以周末作为一个循环
select date_format(now(),'%Y%U');
select date_format('20220108090109','%Y%U');

2.2 字符串转日期

# 日期格式与表达式格式一致即可
SELECT STR_TO_DATE('07/15/2024', '%m/%d/%Y'); # 2024-07-15
SELECT STR_TO_DATE('20240715213508', '%Y%m%d%H%i%s'); # 2024-07-15 21:35:08

三：日期间隔

3.1增加日期间隔

# 间隔单位可以是DAY MONTH MINUTE WEEK YEAR SECOND HOUR
SELECT DATE_ADD(NOW(),INTERVAL 2 DAY); # 2024-07-17 21:38:18
SELECT DATE_ADD(NOW(),INTERVAL 2 MONTH); # 2024-09-15 21:38:44
SELECT DATE_ADD('2022-02-06 22:47:17',INTERVAL 2 MONTH); # 2024-09-15 21:39:40

 3.2减去一个时间间隔

SELECT DATE_SUB(NOW(),INTERVAL 3 DAY); # 2024-07-12 21:40:50
SELECT DATE_SUB('2024-07-12 21:40:50',INTERVAL 2 MONTH); # 2024-05-12 21:40:50

3.3日期相差天数

select datediff('2024-07-15','2024-07-12');

3.4相差时间（小时）

select timediff('2024-07-15 21:39:40', '2024-07-18 21:39:40'); -- -72:00:00

四：星期操作

# 返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。
SELECT DAYOFWEEK(NOW())-1;

# 返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)
SELECT WEEKDAY(NOW())+1

五：其他操作

# 获取日
SELECT DAYOFMONTH(NOW());# 15
# 获取月份
SELECT MONTH(NOW());# 7
# 获取星期几
SELECT DAYNAME(NOW());# Monday
# 获取第几季度
SELECT QUARTER(NOW());# 2024/7/15 --> 3

 六：总结

使用 SQL 中的日期函数具有以下显著的好处：

 

1. 提高数据准确性：

 

• 确保在处理日期数据时遵循正确的格式和逻辑，减少因手动处理导致的错误。
• 例如，使用内置的日期验证函数可以防止输入不合法的日期值，如 ISDATE() 函数。

 

2. 增强查询灵活性：

 

• 能够根据各种日期条件进行精确的筛选和查询。
• 比如，要获取本月的销售数据，可以使用日期函数轻松实现。

 

3. 简化数据处理：

 

• 无需手动编写复杂的逻辑来处理日期的计算、转换和比较。
• 像计算两个日期之间的工作日数量，使用相应的日期函数会简单很多。

 

4. 优化性能：

 

• 数据库系统对内置的日期函数进行了优化，执行效率通常较高。
• 相较于自定义的复杂逻辑，使用日期函数能更有效地利用数据库资源。

 

5. 统一和标准化：

 

• 所有使用该数据库的应用都能依赖相同的日期函数进行操作，保证结果的一致性。

 

6. 适应业务需求变化：

 

• 当业务规则中对日期的处理要求发生改变时，只需修改使用日期函数的相关部分，而不必更改大量的底层代码。

 

举例来说，在 SQL Server 中，如果要获取当前日期和时间，可以直接使用 GETDATE() 函数。如果要计算某个订单表中，订单创建日期距离当前日期超过 30 天的记录，可以使用 DATEDIFF(day, OrderCreationDate, GETDATE()) > 30 这样的条件来快速筛选，大大提高了数据处理的效率和准确性。