DATEDIFF() & DATEDIFF_BIG()函數(計算兩個日期之間的時間間隔)
SQL
DATEDIFF() 語法 (Syntax)
DATEDIFF(datepart, startdate, enddate)
datepart：返回值的時間單位（見下方表格）。
startdate：開始日期（較早的日期）。
enddate：結束日期（較晚的日期）。
返回值：整數，表示 enddate - startdate 的差值（以指定的 datepart 為單位）。datepart 常用的值
| datepart (全名) | 縮寫 | 說明 |
|---|---|---|
| year | yy, yyyy | 年 |
| quarter | qq, q | 季度 |
| month | mm, m | 月 |
| dayofyear | dy, y | 一年中的第幾天 |
| day | dd, d | 日 |
| week | wk, ww | 週 |
| hour | hh | 小時 |
| minute | mi, n | 分鐘 |
| second | ss, s | 秒 |
| millisecond | ms | 毫秒 |
範例
SQL
-- 計算相差天數
SELECT DATEDIFF(day, '2024-01-01', '2024-12-31');
-- 結果：365（2024 年是閏年）
SELECT DATEDIFF(day, '2024-11-22', '2024-11-29');
-- 結果：7
-- 計算相差月數
SELECT DATEDIFF(month, '2024-01-01', '2024-06-01');
-- 結果：5
SELECT DATEDIFF(month, '2024-01-31', '2024-02-01');
-- 結果：1（即使只差 1 天，但跨月了）
-- 計算相差年數
SELECT DATEDIFF(year, '2020-12-31', '2024-01-01');
-- 結果：4（注意：這是年份的差異，不是完整年數）
SELECT DATEDIFF(year, '2020-01-01', '2024-12-31');
-- 結果：4
-- 計算相差小時數
SELECT DATEDIFF(hour, '2024-11-22 08:00:00', '2024-11-22 17:30:00');
-- 結果：9
SELECT DATEDIFF(hour, '2024-11-22 00:00:00', '2024-11-23 00:00:00');
-- 結果：24
-- 計算相差分鐘數
SELECT DATEDIFF(minute, '2024-11-22 10:00:00', '2024-11-22 10:45:30');
-- 結果：45
SELECT DATEDIFF(minute, '2024-01-01', '2024-01-02');
-- 結果：1440（24 小時 × 60 分鐘）
-- 計算相差秒數
SELECT DATEDIFF(second, '2024-11-22 10:00:00', '2024-11-22 10:01:30');
-- 結果：90
-- 負數結果
當 startdate 晚於 enddate 時，返回負數：
SELECT DATEDIFF(day, '2024-12-31', '2024-01-01');
-- 結果：-365CONVERT() & FORMAT()函數(轉換與格式化日期)
SQL
CONVERT() 語法 (Syntax)
CONVERT(data_type[(length)], expression [, style])
data_type：目標資料型別（如 varchar, datetime, date 等）。
length：可選，指定目標資料型別的長度（如 varchar(20)）。
expression：要轉換的來源值。
style：可選，指定日期時間的顯示格式（見下方表格）。日期時間常用格式的 style 值
| style (不含世紀 yy) | style (含世紀 yyyy) | 說明 | 輸出格式範例 |
|---|---|---|---|
| – | 0 或 100 | 預設值 | Nov 22 2024 2:30PM |
| 1 | 101 | 美國格式 | 11/22/2024 |
| 2 | 102 | ANSI | 2024.11.22 |
| 6 | 106 | – | 22 Nov 2024 |
| 8 或 108 | 8 或 108 | 24 小時制時間 | 14:30:45 |
| – | 9 或 109 | 預設 + 毫秒 | Nov 22 2024 2:30:45:123PM |
| 11 | 111 | 日本格式 | 2024/11/22 |
| 12 | 112 | ISO 格式 | 20241122 |
| – | 20 或 120 | ODBC 標準 | 2024-11-22 14:30:45 |
範例
SQL
--基本日期格式轉換
-- 預設格式
SELECT CONVERT(varchar, GETDATE(), 100);
-- 結果：Nov 22 2024 2:30PM
-- 美國格式 mm/dd/yyyy
SELECT CONVERT(varchar, GETDATE(), 101);
-- 結果：11/22/2024
-- 日本格式 yyyy/mm/dd
SELECT CONVERT(varchar, GETDATE(), 111);
-- 結果：2024/11/22
-- ISO 格式 yyyymmdd
SELECT CONVERT(varchar, GETDATE(), 112);
-- 結果：20241122
--只取時間部分
SELECT CONVERT(varchar, GETDATE(), 108);
-- 結果：14:30:45
-- 產生檔案名稱
SELECT 'backup_' + CONVERT(varchar, GETDATE(), 112) + '_' +
REPLACE(CONVERT(varchar, GETDATE(), 108), ':', '') + '.bak' AS backup_filename;
-- 結果：backup_20241122_143045.bak
-- 日誌記錄時間戳記
INSERT INTO logs (message, created_at_string)
VALUES ('User logged in', CONVERT(varchar, GETDATE(), 121));
-- 將字串轉換為日期
SELECT CONVERT(datetime, '2024-11-22');
-- 結果：2024-11-22 00:00:00.000
SELECT CONVERT(datetime, '11/22/2024', 101);
-- 結果：2024-11-22 00:00:00.000
SELECT CONVERT(datetime, '22/11/2024', 103);
-- 結果：2024-11-22 00:00:00.000
-- 現行年月
SELECT CONVERT(char(6),GETDATE(),112);
-- 結果：202609
-- 現行年月 的 上一個月
SELECT CONVERT(char(6), DATEADD(MONTH,-1,GETDATE()), 112);
-- 結果：202608
-- 查詢上一個月的打卡紀錄
SELECT *
FROM [dbo].[AttendanceCollect]
WHERE CONVERT(char(6),AttendanceDate) = CONVERT(char(6), DATEADD(MONTH,-1,GETDATE()), 112) -- 資料日期=上一個月
-- 使用 FORMAT() 函數（自訂格式靈活、但效能稍慢）
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd');
-- 輸出結果：2026-09-15
SELECT FORMAT(GETDATE(), 'yyyy年MM月dd日');
-- 輸出結果：2026年09月15日
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd');
-- 結果：2024-11-22
SELECT FORMAT(GETDATE(), 'yyyy年MM月dd日');
-- 結果：2024年11月22日
SELECT FORMAT(GETDATE(), 'dddd, MMMM d, yyyy');
-- 結果：Friday, November 22, 2024
SELECT FORMAT(GETDATE(), 'HH:mm:ss');
-- 結果：14:30:45CAST() vs CONVERT()
CAST() 是 ANSI SQL 標準，CONVERT() 是 SQL Server 的專有函數：
SQL
-- CAST（不支援 style 參數）
SELECT CAST(GETDATE() AS varchar);
-- 結果：Nov 22 2024 2:30PM
-- CONVERT（支援 style 參數）
SELECT CONVERT(varchar, GETDATE(), 111);
-- 結果：2024/11/22
[…] SQL Server 的 CONVERT() […]