数据库SQL语句的日期数据操作

一、获取当前日期数据

1、Oracle SYSDATE

（1）获取当前日期和时间（最常用）SYSDATE

-- SYSDATE - 返回数据库服务器的当前日期和时间
SELECT SYSDATE FROM DUAL;  -- 2025-12-10 13:45:54
-- CURRENT_DATE - 返回会话时区的当前日期和时间
SELECT CURRENT_DATE FROM DUAL;  -- 根据会话时区返回 2025-12-10 13:46:09

（2）只获取当前日期部分TO_CHAR()

-- 1、使用 TRUNC() 函数  去掉时间部分，只保留日期
SELECT TRUNC(SYSDATE) FROM DUAL;  -- 2025-12-10 00:00:00
-- 2、使用 TO_DATE() 和 TO_CHAR()
-- 方法1：格式化为日期字符串再转回日期  2025-12-10 00:00:00
SELECT TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD'), 'YYYY-MM-DD') FROM DUAL;
-- 方法2：直接格式化 2025-12-10
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS CURRENT_DATE_STR FROM DUAL;

（3）提取日期各部分

SELECT
    SYSDATE AS 当前日期,  -- 2025-12-10 13:52:29
    EXTRACT(YEAR FROM SYSDATE) AS 年,           -- 2025
    EXTRACT(MONTH FROM SYSDATE) AS 月,          -- 12
    EXTRACT(DAY FROM SYSDATE) AS 日,            -- 10
    TO_CHAR(SYSDATE, 'YYYY') AS 年_字符串,       -- 2025
    TO_CHAR(SYSDATE, 'MM') AS 月_字符串,         -- 12
    TO_CHAR(SYSDATE, 'DD') AS 日_字符串,         -- 10
    TO_CHAR(SYSDATE, 'HH24') AS 小时,           -- 13
    TO_CHAR(SYSDATE, 'MI') AS 分钟,             -- 52
    TO_CHAR(SYSDATE, 'SS') AS 秒钟,             -- 29
    TO_CHAR(SYSDATE, 'Q') AS 季度,              -- 4
    TO_CHAR(SYSDATE, 'D') AS 星期几_数字,        -- 4
    TO_CHAR(SYSDATE, 'Day') AS 星期几_名称,      -- Wednesday
    TO_CHAR(SYSDATE, 'WW') AS 年内周数,         -- 50
    TO_CHAR(SYSDATE, 'IW') AS ISO周数           -- 50
FROM DUAL;

2、MySQL CURDATE()

（1）获取当前日期（年月日） CURDATE()

-- CURDATE() - 只返回日期部分
SELECT CURDATE();  -- 2025-12-10
SELECT CURDATE() + 0;  -- 20251210
-- CURRENT_DATE()  是CURDATE()的别名
SELECT CURRENT_DATE();  -- 2025-12-10

（2）获取当前日期和时间 NOW() 和 SYSDATE()

-- NOW() - 返回日期和时间 CURRENT_TIMESTAMP()是NOW()的别名
SELECT NOW();  -- 2025-12-10 11:23:08
SELECT CURRENT_TIMESTAMP();  -- 2025-12-10 11:23:08
-- SYSDATE() - 返回函数执行时的时间（与NOW()略有不同）
SELECT SYSDATE();  -- 2025-12-10 11:23:39

3. 获取当前时间（时分秒）CURTIME()

-- CURTIME() - 只返回时间部分
SELECT CURTIME();  -- 返回：14:30:45
-- CURRENT_TIME() - CURTIME()的别名
SELECT CURRENT_TIME();  -- 返回：14:30:45

3、SQLSever GETDATE()

（1）获取当前日期和时间（最常用）

-- GETDATE() - 返回系统当前日期和时间
SELECT GETDATE()  -- 2025-12-10 13:56:55.903
-- CURRENT_TIMESTAMP - ANSI SQL标准的当前时间戳（与GETDATE()相同）
SELECT CURRENT_TIMESTAMP   -- 返回与GETDATE()相同 2025-12-10 13:59:34.600

（2）只获取当前日期部分
使用 CAST() 或 CONVERT() 转换

-- 方法1：转换为DATE类型（SQL Server 2008+）
SELECT CAST(GETDATE() AS DATE) -- 2025-12-10
-- 方法2：使用CONVERT函数
SELECT CONVERT(DATE, GETDATE())   -- 2025-12-10

二、常用的日期时间函数

1、获取当前日期和时间

SELECT NOW(), CURDATE(), CURTIME();

2、日期加减运算

SELECT 
    NOW() as current_t,
    DATE_ADD(NOW(), INTERVAL 1 DAY) as tomorrow,
    DATE_SUB(NOW(), INTERVAL 1 HOUR) as one_hour_ago,
    DATE_ADD(NOW(), INTERVAL 3 MONTH) as three_months_later;

3、提取日期部分

SELECT 
    NOW() as full_datetime,
    DATE(NOW()) as date_part,
    TIME(NOW()) as time_part,
    YEAR(NOW()) as year1,
    MONTH(NOW()) as month1,
    DAY(NOW()) as day1,
    HOUR(NOW()) as hour1;

4、日期比较

SELECT 
    DATEDIFF('2025-12-31', CURDATE()) as days,
    TIMESTAMPDIFF(HOUR, '2025-01-01 00:00:00', NOW()) as hours;

DATEDIFF(date1, date2)。
作用：计算两个日期之间的天数差。
返回值：date1 - date2（整数天数）
注意：只计算日期部分，忽略时间部分
TIMESTAMPDIFF(unit, datetime1, datetime2)。
作用：计算两个日期时间之间的差值
参数：
unit：时间单位（YEAR, MONTH, DAY, HOUR, MINUTE, SECOND等）
datetime1：开始时间；
datetime2：结束时间。
返回值：datetime2 - datetime1（整数）

三、字符串与时间的转换

1、字符串转日期

（1）Oracle TO_DATE(‘20251112’, ‘YYYYMMDD’)

TO_DATE(‘20251112’, ‘YYYYMMDD’)

SELECT TO_DATE('20251112', 'YYYYMMDD') as strdate FROM dual;
-- 2025-11-12 00:00:00

（2）MySQL STR_TO_DATE(‘20251112’, ‘%Y%m%d’)

STR_TO_DATE(‘20251112’, ‘%Y%m%d’)
CAST(‘20251112’ AS DATE)

SELECT STR_TO_DATE('20251112', '%Y%m%d') ;
-- 2025-11-12
SELECT CAST('20251112' AS DATE);
-- 2025-11-12

-- 基本转换
SELECT STR_TO_DATE('20-07-1995', '%d-%m-%Y') FROM dual;
-- 结果：1995-07-20

-- 不同分隔符
SELECT STR_TO_DATE('20/07/1995', '%d/%m/%Y') FROM dual;
-- 结果：1995-07-20

-- 包含时间的转换
SELECT STR_TO_DATE('20-07-1995 14:30:25', '%d-%m-%Y %H:%i:%s') FROM dual;
-- 结果：1995-07-20 14:30:25

-- 月份名称转换
SELECT STR_TO_DATE('July 20, 1995', '%M %d, %Y') FROM dual;
-- 结果：1995-07-20

常见格式符号

（3）SQLSever CONVERT(DATE, ‘20251112’)

在SQL Server中，可以使用CONVERT(DATE, ‘20251112’) 或 CAST(‘20251112’ AS DATE)函数来替代：

SELECT CONVERT(DATE, '20251112');
-- 2025-11-12
SELECT CAST('20251112' AS DATE);
-- 2025-11-12

说明：
MySQL和SQL Server不需要 FROM dual，可以直接执行SELECT
CONVERT(DATE, string) 直接将字符串转换为日期类型
CAST(string AS DATE) 也是常用的类型转换方式
SQL Server能够自动识别 ‘YYYYMMDD’ 格式的字符串

2、日期转字符串

（1）Oracle TO_CHAR(SYSDATE, ‘YYYYMMDD’)

TO_CHAR(SYSDATE, ‘YYYYMMDD’)

SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYYMMDD') FROM dual;
-- 2025-11-13 15:14:26        20251113

（2）MySQL DATE_FORMAT(NOW(), ‘%Y%m%d’)

DATE_FORMAT(NOW(), ‘%Y%m%d’)

SELECT NOW(),DATE_FORMAT(NOW(), '%Y%m%d');
-- 2025-11-13 15:15:33       20251113

（3）SQL Server FORMAT(GETDATE(), ‘yyyyMMdd’)

FORMAT(GETDATE(), ‘yyyyMMdd’)

SELECT GETDATE(), FORMAT(GETDATE(), 'yyyyMMdd')
-- 2025-11-13 15:21:42.163          20251113

CONVERT(VARCHAR(8), GETDATE(), 112)

SELECT GETDATE(), CONVERT(VARCHAR(8), GETDATE(), 112);
-- 2025-11-13 15:19:06.237         20251113

SQL Server 的 CONVERT 函数常用的日期样式代码：

• 112: YYYYMMDD (8位数字)
• 111: YYYY/MM/DD
• 110: MM-DD-YYYY
• 120: YYYY-MM-DD HH:MI:SS

四、字符串截取；日期截取

（1）Oracle

在Oracle数据库中，将 ‘2025-06-21 00:00:00’ 字符串截取并转换成 ‘20250621’ 格式，有以下几种方法：
SUBSTR(‘字符串’, 起始位置, 长度)

SUBSTR() 函数用于从字符串中提取一个子字符串。
SUBSTR(str, pos, len)
参数解释：
str： 源字符串。可以是字符常量、列名或变量。
pos： 开始截取的位置。
如果为正数，则表示从字符串的左侧开始计算（第一个字符的位置是 1）。
如果为负数，则表示从字符串的右侧开始计算（最后一个字符的位置是 -1）。
len： 可选参数。指定要截取的子字符串的长度。
如果省略此参数，SUBSTR() 会从 start_position 位置开始，一直截取到字符串的末尾。
如果指定的长度大于剩余字符数，则返回从开始位置到字符串末尾的所有字符。

SELECT SUBSTR('2025/09/17 01:02:00.000', 1, 8) FROM dual;
-- 2025/09/

方法1：使用TO_DATE和TO_CHAR（推荐）

-- 完整转换：字符串→日期→格式化字符串
SELECT TO_CHAR(TO_DATE('2025-06-21 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'YYYYMMDD') 
FROM DUAL;
-- 结果：20250621

方法2：使用REPLACE函数，直接字符串截取和替换（如果确定格式固定）
REPLACE() 函数用于将字符串中所有出现的指定子字符串替换为另一个新的子字符串。

REPLACE( string, search_string [, replacement_string] )
参数解释：
string： 源字符串。
search_string： 要被查找和替换的子字符串。
replacement_string： 可选参数。用于替换 search_string 的新字符串。
如果省略此参数，或者将其设置为 NULL，则所有出现的 search_string 都将被移除（即替换为空字符串 ‘’）。

SELECT REPLACE(SUBSTR('2025-06-21 00:00:00', 1, 10), '-', '') FROM DUAL;
-- 结果：20250621
SELECT REPLACE(SUBSTR('2025/09/17 01:02:00.000', 1, 10), '/', '')  FROM dual;
-- 20250917

TRUNC 函数是 Oracle 中用于截断日期或数字的函数，这里主要讲解其在日期处理中的应用。

-- 假设当前日期时间：2025-11-19 10:05:49
SELECT 
    SYSDATE AS t,                      -- 2025-11-19 10:05:49
    TRUNC(SYSDATE) AS t_day,           -- 2025-11-19 00:00:00
    TRUNC(SYSDATE, 'HH24') AS t_hour,  -- 2025-11-19 10:00:00
    TRUNC(SYSDATE, 'MI') AS t_minute,  -- 2025-11-19 10:05:00
    TRUNC(SYSDATE, 'DAY') AS t_week,   -- 2025-11-16 00:00:00 (本周第一天：周日)
    TRUNC(SYSDATE, 'IW') AS t_week_monday, -- 2025-11-17 00:00:00 (周一)
    TRUNC(SYSDATE, 'MM') AS t_month,   -- 2025-11-01 00:00:00 (本月第一天)
    TRUNC(SYSDATE, 'Q') AS t_quarter,  -- 2025-10-01 00:00:00 (本季度第一天)
    TRUNC(SYSDATE, 'YEAR') AS t_year   -- 2025-01-01 00:00:00 (本年第一天)
FROM dual;

（2）MySQL

SUBSTR(‘字符串’, 起始位置, 长度)
SUBSTRING(‘字符串’, 起始位置, 长度)
在 MySQL 中，SUBSTR() 和 SUBSTRING() 是完全相同的函数，它们之间没有任何功能上的区别。

SUBSTR(str, pos, len)

SELECT SUBSTR('2025/09/17 01:02:00.000', 1, 8);
-- 结果: '2025/09/'
-- 方法1：日期转换
SELECT DATE_FORMAT(STR_TO_DATE('2025/09/17 01:02:00.000', '%Y/%m/%d %H:%i:%s.%f'), '%Y%m%d');
-- 20250917
-- 方法2：字符串处理（推荐）
SELECT REPLACE(SUBSTRING('2025/09/17 01:02:00.000', 1, 10), '/', '');
-- 20250917

（3）SQL Server

SUBSTRING(‘字符串’, 起始位置, 长度)

SELECT SUBSTRING('2025/09/17 01:02:00.000', 1, 8);
-- 结果: '2025/09/'
-- 提取日期数据
-- 方法1：日期转换  20250917
SELECT FORMAT(CONVERT(datetime2, '2025/09/17 01:02:00.000'), 'yyyyMMdd');
-- 方法2：字符串处理（推荐）  20250917
SELECT REPLACE(SUBSTRING('2025/09/17 01:02:00.000', 1, 10), '/', '');
-- 方法3：CONVERT 格式  20250917
SELECT CONVERT(varchar(8), CONVERT(datetime, '2025/09/17 01:02:00.000'), 112);

函数语法对比

注意要点：
起始位置都是从1开始计数
截取长度指定要返回的字符数
如果字符串长度不足8位，会返回整个字符串而不会报错
在实际应用中，建议先检查字符串长度，避免意外结果