当前位置：首页 > news >正文

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

news 2025/10/21 20:56:08

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

1. 获取当前日期和时间

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

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 -- 加减天数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. 日期格式转换

SELECT -- 转换为字符串CONVERT(VARCHAR, GETDATE(), 120) AS Format_120,      -- yyyy-mm-dd hh:mi:ssCONVERT(VARCHAR, GETDATE(), 112) AS Format_112,      -- yyyymmddCONVERT(VARCHAR, GETDATE(), 23) AS Format_23,        -- yyyy-mm-ddCONVERT(VARCHAR, GETDATE(), 101) AS Format_101,      -- mm/dd/yyyyCONVERT(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. 日期部分获取和设置

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. 日期验证和判断

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

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

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;