How to Generate a 100-Year Calendar Table in SQL?

Creating a 100-year calendar table in SQL Server

The following generic script creates a calendar table spanning 100 years in SQL Server:

<code class="language-sql">IF EXISTS (SELECT * FROM information_schema.tables WHERE Table_Name = 'Calendar' AND Table_Type = 'BASE TABLE')
BEGIN
    DROP TABLE [Calendar]
END

CREATE TABLE [Calendar] (
    [CalendarDate] DATETIME
)

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = GETDATE()
SET @EndDate = DATEADD(year, 100, @StartDate) -- 修改为100年

WHILE @StartDate <= @EndDate
BEGIN
    INSERT INTO [Calendar] (CalendarDate)
    SELECT @StartDate

    SET @StartDate = DATEADD(day, 1, @StartDate)
END</code>

Remember to adjust the start and end dates in the script to your specific needs. This code will generate a calendar table containing 100 years of data.

For more advanced calendar implementation, you can refer to other scripts that generate complete calendars with various attributes such as year, quarter, week, date, and holiday.

The above is the detailed content of How to Generate a 100-Year Calendar Table in SQL?. For more information, please follow other related articles on the PHP Chinese website!