How to Create a 100-Year Calendar Table in SQL for Efficient Date-Related Queries?

Create a calendar table covering 100 years in SQL

Suppose you have a data set containing a large number of dates and need to extract specific information, such as the day of the year or the number of weeks. Creating a calendar table allows you to easily retrieve this information by connecting your data with the calendar table. Here's how to create a calendar table covering a 100-year period in SQL:

-- 开始日期：1901年1月1日
-- 结束日期：2099年12月31日
DECLARE @StartDate DATETIME = '19010101';
DECLARE @EndDate DATETIME = '20991231';

-- 创建日历表
CREATE TABLE Calendar (
    CalendarDate DATETIME NOT NULL
);

-- 将日期插入日历表
WHILE @StartDate <= @EndDate
BEGIN
    INSERT INTO Calendar (CalendarDate) VALUES (@StartDate);
    SET @StartDate = DATEADD(day, 1, @StartDate);
END;

This script will create a calendar containing every day between January 1, 1901 and December 31, 2099. You can then use this table to extract date-related information from the data. For example, the following query will return the number of days between two dates:

SELECT DATEDIFF(day, StartDate, EndDate)
FROM Calendar
WHERE StartDate >= '20230101' AND EndDate <= '20230331';

The result of this query will be 90 because there are 90 days between January 1, 2023 and March 31, 2023.

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