关系型数据库日期处理函数讲解

在 MySQL、Oracle 和 SQL Server 中，日期处理函数是很常用的功能，用于处理和操作日期时间数据。但三个数据库中的相关语句又有差别，因此整理了一下个人开发中常用的有关日期时间的函数或解决思路，仅供参考！

一、获取当前日期和时间

1. MySQL

• 函数：NOW()

• 案例：

  SELECT NOW(); -- 获取当前日期和时间
  

2. Oracle

• 函数：SYSDATE

• 案例：

  SELECT SYSDATE FROM dual; -- 获取当前日期和时间
  

3. SQL Server

• 函数：GETDATE()

• 案例：

  SELECT GETDATE(); -- 获取当前日期和时间
  

二、提取日期的各部分信息

1. MySQL

• 函数：YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND()

• 案例：

  SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW()), HOUR(NOW()), MINUTE(NOW()), SECOND(NOW());
  -- 提取当前日期的年、月、日、小时、分钟和秒
  

2. Oracle

• 函数：EXTRACT()

• 案例：

  SELECT EXTRACT(YEAR FROM SYSDATE), EXTRACT(MONTH FROM SYSDATE), EXTRACT(DAY FROM SYSDATE) FROM dual;
  -- 提取当前日期的年、月、日
  

3. SQL Server

• 函数：YEAR(), MONTH(), DAY()

• 案例：

  SELECT YEAR(GETDATE()), MONTH(GETDATE()), DAY(GETDATE());
  -- 提取当前日期的年、月、日
  

三、日期加减操作

1. MySQL

• 函数：DATE_ADD(), DATE_SUB()

• 案例：

  SELECT DATE_ADD(NOW(), INTERVAL 1 DAY); -- 日期加一天
  SELECT DATE_SUB(NOW(), INTERVAL 1 DAY); -- 日期减一天
  

2. Oracle

• 函数：SYSDATE + n（天数加减）

• 案例：

  sql复制代码SELECT SYSDATE + 1 FROM dual; -- 日期加一天
  SELECT SYSDATE - 1 FROM dual; -- 日期减一天
  

3. SQL Server

• 函数：DATEADD()

• 案例：

  SELECT DATEADD(DAY, 1, GETDATE()); -- 日期加一天
  SELECT DATEADD(DAY, -1, GETDATE()); -- 日期减一天
  

四、日期差值计算

1. MySQL

• 函数：DATEDIFF()

• 案例：

  SELECT DATEDIFF('2024-12-31', NOW()); -- 计算当前日期到2024-12-31的天数差
  

2. Oracle

• 函数：日期相减

• 案例：

  SELECT SYSDATE - TO_DATE('2024-12-31', 'YYYY-MM-DD') FROM dual; -- 计算日期差
  

3. SQL Server

• 函数：DATEDIFF()

• 案例：

  SELECT DATEDIFF(DAY, GETDATE(), '2024-12-31'); -- 计算当前日期到2024-12-31的天数差
  

五、日期格式化

1. MySQL

• 函数：DATE_FORMAT()

• 案例：

  SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- 格式化日期为指定格式
  

2. Oracle

• 函数：TO_CHAR()

• 案例：

  SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual; -- 格式化日期为指定格式
  

3. SQL Server

• 函数：FORMAT()

• 案例：

  SELECT FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss'); -- 格式化日期为指定格式
  

六、日期字符串转换为日期类型

1. MySQL

• 函数：STR_TO_DATE()

• 案例：

  SELECT STR_TO_DATE('2024-12-31', '%Y-%m-%d'); -- 将字符串转换为日期
  

2. Oracle

• 函数：TO_DATE()

• 案例：

  SELECT TO_DATE('2024-12-31', 'YYYY-MM-DD') FROM dual; -- 将字符串转换为日期
  

3. SQL Server

• 函数：CAST() 或 CONVERT()

• 案例：

  SELECT CAST('2024-12-31' AS DATE); -- 将字符串转换为日期
  SELECT CONVERT(DATE, '2024-12-31', 120); -- 将字符串转换为日期
  

七、获取日期的星期信息

1. MySQL

• 函数：DAYOFWEEK(), WEEKDAY(), WEEK()

• 案例：

  SELECT DAYOFWEEK(NOW()); -- 获取当前日期是星期几，返回1（周日）到7（周六）
  SELECT WEEKDAY(NOW()); -- 获取当前日期是星期几，返回0（周一）到6（周日）
  SELECT WEEK(NOW()); -- 获取当前日期是当前年的第几周
  

2. Oracle

• 函数：TO_CHAR()

• 案例：

  SELECT TO_CHAR(SYSDATE, 'D') FROM dual; -- 获取当前日期是星期几，返回1（周日）到7（周六）
  SELECT TO_CHAR(SYSDATE, 'IW') FROM dual; -- 获取当前日期是ISO标准下的第几周
  

3. SQL Server

• 函数：DATENAME(), DATEPART()

• 案例：

  SELECT DATENAME(WEEKDAY, GETDATE()); -- 获取当前日期的星期名称
  SELECT DATEPART(WEEK, GETDATE()); -- 获取当前日期是当前年的第几周
  

八、设置特定的日期和时间

1. MySQL

• 函数：MAKEDATE(), MAKETIME()

• 案例：

  SELECT MAKEDATE(2024, 150); -- 根据年份和天数生成日期，2024年第150天
  SELECT MAKETIME(12, 30, 45); -- 创建时间为12:30:45
  

2. Oracle

• 函数：TO_DATE(), TO_TIMESTAMP()

• 案例：

  SELECT TO_DATE('2024-12-31', 'YYYY-MM-DD') FROM dual; -- 创建一个特定的日期
  SELECT TO_TIMESTAMP('2024-12-31 12:30:45', 'YYYY-MM-DD HH24:MI:SS') FROM dual; -- 创建一个特定的时间戳
  

3. SQL Server

• 函数：DATETIMEFROMPARTS(), TIMEFROMPARTS()

• 案例：

  SELECT DATETIMEFROMPARTS(2024, 12, 31, 12, 30, 45, 0); -- 创建一个特定的日期时间
  SELECT TIMEFROMPARTS(12, 30, 45, 0); -- 创建一个特定的时间
  

九、获取日期中的季度信息

1. MySQL

• 函数：QUARTER()

• 案例：

  SELECT QUARTER(NOW()); -- 获取当前日期是第几季度
  

2. Oracle

• 函数：EXTRACT()

• 案例：

  SELECT EXTRACT(QUARTER FROM SYSDATE) FROM dual; -- 获取当前日期是第几季度
  

3. SQL Server

• 函数：DATEPART()

• 案例：

  SELECT DATEPART(QUARTER, GETDATE()); -- 获取当前日期是第几季度
  

十、获取月份的天数

MySQL和Oracle在网上有建议使用LAST_DAY()函数的，但是我用的库一直报错(猜测可能是版本原因)，这里提供一个个人常用的思路，仅供参考：查询当月下个月第一天的日期，然后查询该日期前一天的日期。

1. MySQL

SELECT 
DATE_SUB(
    DATE_ADD(CURDATE(), INTERVAL 1 MONTH), INTERVAL DAY(CURDATE()) DAY
    ) AS last_day_of_month;

解释：

• CURDATE()：返回当前的日期。
• DATE_ADD(CURDATE(), INTERVAL 1 MONTH)：获取下个月的同一天。
• DAY(CURDATE())：获取当前日期的天数。
• DATE_SUB(..., INTERVAL DAY(CURDATE()) DAY)：从下个月的同一天减去当前天数，得到当月的最后一天。

示例：

假设今天是 2024-10-08，查询结果将是：

2024-10-31

2. Oracle

SELECT TRUNC(ADD_MONTHS(SYSDATE, 1), 'MM') - 1 AS last_day_of_month
FROM dual;

解释：

• SYSDATE：获取当前日期和时间。
• ADD_MONTHS(SYSDATE, 1)：获取下个月的同一天。
• TRUNC(ADD_MONTHS(SYSDATE, 1), 'MM')：将下个月的日期截取为月初（即下个月的第一天）。
• - 1：从下个月的第一天减去一天，得到当月的最后一天。

示例：

假设今天是 2024-10-08，查询结果将是：

2024-10-31

3. SQL Server

SELECT EOMONTH(GETDATE()) AS last_day_of_month;

解释：

• GETDATE()：获取当前日期和时间。
• EOMONTH(GETDATE())：获取当前月份的最后一天。

示例：

假设今天是 2024-10-08，查询结果将是：

2024-10-31

十一、日期与时间戳的转换

1. MySQL

• 函数：UNIX_TIMESTAMP(), FROM_UNIXTIME()

• 案例：

  SELECT UNIX_TIMESTAMP(NOW()); -- 将当前时间转换为UNIX时间戳
  SELECT FROM_UNIXTIME(1672531199); -- 将UNIX时间戳转换为日期时间
  

2. Oracle

• 函数：CURRENT_TIMESTAMP, TIMESTAMP

• 案例：

  SELECT CURRENT_TIMESTAMP FROM dual; -- 获取当前时间戳
  SELECT TO_TIMESTAMP('2024-12-31 12:30:45', 'YYYY-MM-DD HH24:MI:SS') FROM dual; -- 将字符串转换为时间戳
  

3. SQL Server

• 函数：GETUTCDATE(), SWITCHOFFSET()

• 案例：

  SELECT GETUTCDATE(); -- 获取当前UTC时间
  SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'); -- 将UTC时间偏移为特定时区的时间
  

十二、查询不同日期固定时间

在开发中可能会遇到查询不同日期固定时间的情况，例如查询当天的08:00:00的情况，下面通过一些例子记录个人解决的思路

1. MySQL

SELECT DATE_FORMAT(CURDATE(), '%Y-%m-%d 08:00:00');

2. Oracle

SELECT TO_CHAR(TRUNC(SYSDATE), 'YYYY-MM-DD') || ' 08:00:00' FROM dual;

3. SQL Server

SELECT CONVERT(VARCHAR, GETDATE(), 23) + ' 08:00:00';

十三、其他日期函数

1. MySQL

• 函数：ADDDATE(), SUBDATE()

• 案例：

  SELECT ADDDATE(NOW(), INTERVAL 5 DAY); -- 在当前日期加上5天
  SELECT SUBDATE(NOW(), INTERVAL 5 DAY); -- 从当前日期减去5天
  

2. Oracle

• 函数：ADD_MONTHS(), NEXT_DAY()

• 案例：

  SELECT ADD_MONTHS(SYSDATE, 2) FROM dual; -- 在当前日期加上2个月
  SELECT NEXT_DAY(SYSDATE, 'MONDAY') FROM dual; -- 获取当前日期之后的下一个星期一
  

3. SQL Server

• 函数：EOMONTH(), SYSDATETIME()

• 案例：

  SELECT EOMONTH(GETDATE()); -- 获取当前月的最后一天
  SELECT SYSDATETIME(); -- 获取当前系统的日期和时间