sqlserver 主要的日期函数及用法示例

SQL Server 主要日期函数及用法示例

1. 获取当前日期和时间

sql

SELECT 
    GETDATE() AS CurrentDateTime,           -- 当前日期时间
    GETUTCDATE() AS CurrentUTCDateTime,     -- 当前UTC时间
    SYSDATETIME() AS SystemDateTime,        -- 更高精度的系统时间
    CURRENT_TIMESTAMP AS CurrentTimestamp;  -- ANSI SQL标准写法

2. 日期部分提取

sql

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. 日期加减计算

sql

SELECT 
    -- 加减天数
    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. 日期差异计算

sql

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. 日期格式转换

sql

SELECT 
    -- 转换为字符串
    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. 日期部分获取和设置

sql

SELECT 
    -- 获取日期名称
    DATENAME(YEAR, GETDATE()) AS YearName,
    DATENAME(MONTH, GETDATE()) AS MonthName,
    DATENAME(WEEKDAY, GETDATE()) AS WeekdayName,
    
    -- 从日期创建新日期
    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. 日期验证和判断

sql

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. 实用日期查询示例

sql

-- 查询今天的数据
SELECT * FROM Orders WHERE CAST(OrderDate AS DATE) = CAST(GETDATE() AS DATE);

-- 查询最近7天的数据
SELECT * FROM Orders 
WHERE OrderDate >= DATEADD(DAY, -7, CAST(GETDATE() AS DATE))
  AND OrderDate < DATEADD(DAY, 1, CAST(GETDATE() AS DATE));

-- 查询本月的数据
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. 时间部分操作

sql

SELECT 
    -- 获取时间部分
    CAST(GETDATE() AS TIME) AS TimePart,
    
    -- 设置特定时间
    DATEADD(HOUR, 9, CAST(CAST(GETDATE() AS DATE) AS DATETIME)) AS Today9AM,
    
    -- 计算时间差（分钟）
    DATEDIFF(MINUTE, '09:00', '17:30') AS WorkMinutes;

这些函数涵盖了 SQL Server 中日期时间处理的主要场景，可以根据具体需求选择合适的函数组合使用。