SQL语法 - 日期的操作

日期元素的提取

操作	MySQL	SQL Server
获取当前日期时间	SELECT NOW() — 获取当前具体的日期和时间 2019-11-13 16:38:20	SELECT GETDATE()
获取当前日期	SELECT CURDATE() — 获取当前日期 2019-11-13	SELECT CONVERT(DATE, GETDATE())
获取当前时间	SELECT CURTIME() — 获取当前时间 6:38:20	SELECT CONVERT(TIME, GETDATE())
提取日期部分	SELECT DATE(‘2003-12-31 01:02:03’)	SELECT CAST(‘2003-12-31 01:02:03’ AS DATE)
提取时间部分	SELECT TIME(‘2003-12-31 01:02:03’)	SELECT CAST(‘2003-12-31 01:02:03’ AS TIME)
提取年份	SELECT YEAR(‘2023-06-12’)	SELECT DATEPART(YEAR, ‘2023-06-12 14:30:45’)
提取季度	SELECT QUARTER(‘2024-10-1’)	SELECT DATEPART(QUARTER, ‘2024-10-1’)
提取月份	SELECT MONTH(‘2023-06-12’)	SELECT DATEPART(MONTH, ‘2023-06-12 14:30:45’)
提取年周	SELECT WEEK(‘2008-02-20’,1)，周起始：0周日，1周一	SELECT DATEPART(WEEK, ‘2008-02-01’)
提取星期	SELECT WEEKDAY(‘2007-11-06’) , 0周一, 1周二)	SELECT DATEPART(WEEKDAY, ‘2023-11-06’)
提取天数	SELECT DAY(‘2023-06-12’)	SELECT DATEPART(DAY, ‘2023-06-12 14:30:45’)
提取时间	SELECT HOUR(‘2023-06-12 14:30:45’)	SELECT DATEPART(HOUR, ‘2023-06-12 14:30:45’)
提取分钟	SELECT MINUTE(‘2023-06-12 14:30:45’)	SELECT DATEPART(MINUTE, ‘2023-06-12 14:30:45’)
提取秒数	SELECT SECOND(‘2023-06-12 14:30:45’)	SELECT DATEPART(SECOND, ‘2023-06-12 14:30:45’)

日期的平移与计算

1、日期平移

MySQL	SQL Server
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY); — ADD 1 DAY	SELECT DATEADD(DAY, 1, GETDATE()); — Add 1 day
SELECT DATE_ADD(NOW(), INTERVAL 1 HOUR); — ADD 1 HOUR	SELECT DATEADD(HOUR, 1, GETDATE()); — Add 1 hour
SELECT DATE_ADD(NOW(), INTERVAL 1 MINUTE);	SELECT DATEADD(MINUTE, 1, GETDATE());
SELECT DATE_ADD(NOW(), INTERVAL 1 SECOND);	SELECT DATEADD(SECOND, 1, GETDATE());
SELECT DATE_ADD(NOW(), INTERVAL 1 MICROSECOND);	SELECT DATEADD(MILLISECOND, 1, GETDATE());
SELECT DATE_ADD(NOW(), INTERVAL 1 WEEK);	SELECT DATEADD(WEEK, 1, GETDATE());
SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH);	SELECT DATEADD(MONTH, 1, GETDATE());
SELECT DATE_ADD(NOW(), INTERVAL 1 QUARTER);	SELECT DATEADD(QUARTER, 1, GETDATE());
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);	SELECT DATEADD(YEAR, 1, GETDATE());
SELECT DATE_ADD(NOW(), INTERVAL -1 DAY); — SUB 1 DAY	SELECT DATEADD(DAY, -1, GETDATE()); — Sub 1 day
SELECT DATE_SUB(NOW(), INTERVAL 1 DAY); — SUB 1 DAY

2、日期相减

MySQL	SQL Server
SELECT TIMESTAMPDIFF(YEAR, ‘2002-05-01’, ‘2001-01-01’); — -1	SELECT DATEDIFF(YEAR, ‘2001-01-01’, ‘2002-05-01’); — 1
SELECT TIMESTAMPDIFF(DAY, ‘2002-05-01’, ‘2001-01-01’); — -485	SELECT DATEDIFF(DAY, ‘2001-01-01’, ‘2002-05-01’); — 485
SELECT TIMESTAMPDIFF(HOUR, ‘2008-08-08 12:00:00’, ‘2008-08-08 00:00:00’); — -12	SELECT DATEDIFF(HOUR, ‘2008-08-08 00:00:00’, ‘2008-08-08 12:00:00’); — 12
SELECT DATEDIFF(‘2008-08-08’, ‘2008-08-01’); — 7	SELECT DATEDIFF(DAY, ‘2008-08-01’, ‘2008-08-08’); — 7
SELECT DATEDIFF(‘2008-08-01’, ‘2008-08-08’); — -7	SELECT DATEDIFF(DAY, ‘2008-08-08’, ‘2008-08-01’); — -7
SELECT TIMEDIFF(’08:08:08′, ’00:00:00′); — 08:08:08	SELECT CONVERT(CHAR(8), DATEADD(SECOND, DATEDIFF(SECOND, ’00:00:00′, ’08:08:08′), 0), 108); — 08:08:08

日期与字符串的互相转换

操作	MySQL	SQL Server
日期转字符串	SELECT DATE_FORMAT(now(),’%Y-%m-%d %H:%i:%s’); 结果：2017-10-29 14:02:54	SELECT FORMAT(GETDATE(), ‘yyyy-MM-dd HH:mm:ss’);
字符串转日期	SELECT STR_TO_DATE(‘2017-10-29’, ‘%Y-%m-%d %H:%i:%s’); 结果:2017-10-29 00:00:00	SELECT CAST(‘2017-10-29 00:00:00’ AS DATETIME);