SQL Server 主要日期函數(shù)及用法示例

1. 獲取當(dāng)前日期和時間

SELECT 
    GETDATE() AS CurrentDateTime,           -- 當(dāng)前日期時間
    GETUTCDATE() AS CurrentUTCDateTime,     -- 當(dāng)前UTC時間
    SYSDATETIME() AS SystemDateTime,        -- 更高精度的系統(tǒng)時間
    CURRENT_TIMESTAMP AS CurrentTimestamp;  -- ANSI SQL標(biāo)準(zhǔn)寫法

2. 日期部分提取

SELECT 
    YEAR(GETDATE()) AS YearPart,
    MONTH(GETDATE()) AS MonthPart,
    DAY(GETDATE()) AS DayPart,
    DATEPART(YEAR, GETDATE()) AS YearUsingDatepart,
    DATEPART(QUARTER, GETDATE()) AS Quarter,
    DATEPART(WEEK, GETDATE()) AS WeekNumber,
    DATEPART(WEEKDAY, GETDATE()) AS Weekday,
    DATEPART(HOUR, GETDATE()) AS HourPart,
    DATEPART(MINUTE, GETDATE()) AS MinutePart;

3. 日期加減計算

SELECT 
    -- 加減天數(shù)
    DATEADD(DAY, 1, GETDATE()) AS Tomorrow,
    DATEADD(DAY, -1, GETDATE()) AS Yesterday,
    
    -- 加減月份
    DATEADD(MONTH, 1, GETDATE()) AS NextMonth,
    DATEADD(MONTH, -1, GETDATE()) AS LastMonth,
    
    -- 加減年份
    DATEADD(YEAR, 1, GETDATE()) AS NextYear,
    DATEADD(YEAR, -1, GETDATE()) AS LastYear,
    
    -- 加減小時
    DATEADD(HOUR, 2, GETDATE()) AS TwoHoursLater;

4. 日期差異計算

DECLARE @StartDate DATETIME = '2023-01-01';
DECLARE @EndDate DATETIME = '2023-12-31';

SELECT 
    DATEDIFF(DAY, @StartDate, @EndDate) AS DaysDifference,
    DATEDIFF(MONTH, @StartDate, @EndDate) AS MonthsDifference,
    DATEDIFF(YEAR, @StartDate, @EndDate) AS YearsDifference,
    DATEDIFF(WEEK, @StartDate, @EndDate) AS WeeksDifference,
    DATEDIFF(HOUR, @StartDate, @EndDate) AS HoursDifference;

5. 日期格式轉(zhuǎn)換

SELECT 
    -- 轉(zhuǎn)換為字符串
    CONVERT(VARCHAR, GETDATE(), 120) AS Format_120,      -- yyyy-mm-dd hh:mi:ss
    CONVERT(VARCHAR, GETDATE(), 112) AS Format_112,      -- yyyymmdd
    CONVERT(VARCHAR, GETDATE(), 23) AS Format_23,        -- yyyy-mm-dd
    CONVERT(VARCHAR, GETDATE(), 101) AS Format_101,      -- mm/dd/yyyy
    CONVERT(VARCHAR, GETDATE(), 103) AS Format_103,      -- dd/mm/yyyy
    
    -- 格式化為更友好的格式
    FORMAT(GETDATE(), 'yyyy-MM-dd') AS FormattedDate1,
    FORMAT(GETDATE(), 'dd/MM/yyyy HH:mm:ss') AS FormattedDate2,
    FORMAT(GETDATE(), 'MMMM dd, yyyy') AS FormattedDate3;

6. 日期部分獲取和設(shè)置

SELECT 
    -- 獲取日期名稱
    DATENAME(YEAR, GETDATE()) AS YearName,
    DATENAME(MONTH, GETDATE()) AS MonthName,
    DATENAME(WEEKDAY, GETDATE()) AS WeekdayName,
    
    -- 從日期創(chuàng)建新日期
    DATEFROMPARTS(2023, 12, 25) AS Christmas2023,
    DATETIMEFROMPARTS(2023, 12, 25, 18, 30, 0, 0) AS ChristmasTime,
    
    -- 獲取月份的第一天和最后一天
    DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) AS FirstDayOfMonth,
    EOMONTH(GETDATE()) AS LastDayOfMonth,
    EOMONTH(GETDATE(), 1) AS LastDayOfNextMonth;

7. 日期驗證和判斷

SELECT 
    -- 檢查日期有效性
    ISDATE('2023-02-29') AS IsValidDate1,  -- 0 (2023不是閏年)
    ISDATE('2024-02-29') AS IsValidDate2,  -- 1 (2024是閏年)
    
    -- 判斷工作日（需要自定義邏輯）
    CASE 
        WHEN DATEPART(WEEKDAY, GETDATE()) IN (1, 7) THEN 'Weekend'
        ELSE 'Weekday'
    END AS DayType;

8. 實用日期查詢示例

-- 查詢今天的數(shù)據(jù)
SELECT * FROM Orders WHERE CAST(OrderDate AS DATE) = CAST(GETDATE() AS DATE);

-- 查詢最近7天的數(shù)據(jù)
SELECT * FROM Orders 
WHERE OrderDate >= DATEADD(DAY, -7, CAST(GETDATE() AS DATE))
  AND OrderDate < DATEADD(DAY, 1, CAST(GETDATE() AS DATE));

-- 查詢本月的數(shù)據(jù)
SELECT * FROM Orders 
WHERE OrderDate >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
  AND OrderDate < DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1));

-- 計算年齡
SELECT 
    DATEDIFF(YEAR, BirthDate, GETDATE()) - 
    CASE 
        WHEN DATEADD(YEAR, DATEDIFF(YEAR, BirthDate, GETDATE()), BirthDate) > GETDATE() 
        THEN 1 
        ELSE 0 
    END AS Age
FROM Employees;

9. 時間部分操作

SELECT 
    -- 獲取時間部分
    CAST(GETDATE() AS TIME) AS TimePart,
    
    -- 設(shè)置特定時間
    DATEADD(HOUR, 9, CAST(CAST(GETDATE() AS DATE) AS DATETIME)) AS Today9AM,
    
    -- 計算時間差（分鐘）
    DATEDIFF(MINUTE, '09:00', '17:30') AS WorkMinutes;