sql server 生成全年日历
时间: 2023-05-31 16:04:16 浏览: 49
以下是生成全年日历的 SQL Server 代码:
```
DECLARE @firstDayOfYear DATE = '2022-01-01'
DECLARE @lastDayOfYear DATE = '2022-12-31'
CREATE TABLE #Calendar (
[Date] DATE,
[Year] INT,
[Quarter] INT,
[Month] INT,
[Day] INT,
[DayOfWeek] INT,
[WeekOfYear] INT
)
WHILE @firstDayOfYear <= @lastDayOfYear
BEGIN
INSERT INTO #Calendar (
[Date],
[Year],
[Quarter],
[Month],
[Day],
[DayOfWeek],
[WeekOfYear]
)
SELECT
@firstDayOfYear,
YEAR(@firstDayOfYear),
DATEPART(QUARTER, @firstDayOfYear),
MONTH(@firstDayOfYear),
DAY(@firstDayOfYear),
DATEPART(WEEKDAY, @firstDayOfYear),
DATEPART(WEEK, @firstDayOfYear)
SET @firstDayOfYear = DATEADD(DAY, 1, @firstDayOfYear)
END
SELECT *
FROM #Calendar
ORDER BY [Date]
DROP TABLE #Calendar
```
该代码将生成名为“Calendar”的临时表,其中包含从 2022 年 1 月 1 日到 2022 年 12 月 31 日的所有日期,并为每个日期提供以下信息:
- Year(年份)
- Quarter(季度)
- Month(月份)
- Day(日)
- DayOfWeek(星期几)
- WeekOfYear(当年的第几周)