Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to generate a date dimension table in SQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

Create a table structure

According to the common calendar, we create a calendar containing year, month, day, and week. The specific structure of the table is as follows:

CREATE TABLE CALENDAR_INFO (COUNTRY VARCHAR (5),-- 'country' CDR_CODE VARCHAR (5),-- 'Calendar Code' DATE_NAME VARCHAR (20),-- 'date name' YEAR CHAR (4),-- 'year' MONTH CHAR (2),-- 'month' WEEK CHAR (6),-'week' DAY CHAR (2) -- 'Day' CDR_DATE CHAR (10),-- 'Calendar date' CREATOR VARCHAR (32),-- 'creator' CREATE_DATE DATETIME,-- 'creation date' CREATE_INST VARCHAR (20),-- 'create organization' DEL_FLAG CHAR (1) DEFAULT 0,-- 'Delete flag' 0: do not delete 1: deleted TS DATETIME DEFAULT GETDATE ()-- 'timestamp')

In the above table structure, we display the year, month, week, day, etc. as separate columns in the form of a calendar, and two default values are added at the end, one is the delete flag, the default is 0 for no deletion, and 1 for deletion. Timestamp the system time.

Create a stored procedure

To call parameters, stored procedures are generally used to pass parameters. Let's create a stored procedure based on the requirements.

We pass an int parameter to the stored procedure, indicating the year, and then take the number of days in the year, cycle from January 1 to December 31, and insert the relevant information for each day into the table, so that the complete calendar for the whole year is completed.

Let's start writing this stored procedure in the SQL Server environment.

CREATE PROC PROC_CALENDAR (@ YEAR INT) AS BEGIN DECLARE @ i INT; DECLARE @ START_DATE VARCHAR (20); DECLARE @ END_DATE VARCHAR (20); DECLARE @ DATE_COUNT INT; SET @ iTuno;-- define the start date of the year, and use the CONCAT function to concatenate the year and month dates SET @ START_DATE= CONCAT (@ YEAR,'- 01-01');-- define the end date of the year SET @ END_DATE = CONCAT (@ YEAR+1,'-01-01') -- if the calendar to be created already exists in the table, first delete DELETE FROM CALENDAR_INFO WHERE YEAR = @ YEAR;-- calculate the total number of days from the start to the end date, and the DATEDIFF function calculates the number of days between the date SET @ DATE_COUNT = DATEDIFF (DAY,@START_DATE,@END_DATE);-- establish a loop if @ I is less than the number of days in a year WHILE @ I

< @DATE_COUNT BEGIN INSERT INTO CALENDAR_INFO (COUNTRY,CDR_CODE,DATE_NAME, YEAR,MONTH,WEEK,DAY,CDR_DATE,CREATOR,CREATE_DATE,CREATE_INST) SELECT 'CN', 'CN01', --CONVERT函数将@START_DATE转换成指定的格式 CONVERT(CHAR(10),@START_DATE,111) DATE_NAME, --DATENAME获取@START_DATE的年份 CONVERT(CHAR(4),DATENAME(YEAR, @START_DATE)) YEAR, --DATENAME获取@START_DATE的月份 CONVERT(CHAR(2),DATENAME(MONTH, @START_DATE)) MONTH, --DATENAME获取@START_DATE的星期 CONVERT(CHAR(6),DATENAME(WEEKDAY, @START_DATE)) WEEK, --DATENAME获取@START_DATE的日期 CONVERT(CHAR(2),DATENAME(DAY, @START_DATE)) DAY, CONVERT(CHAR(10),@START_DATE,120) CDR_DATE, '平凡世界', GETDATE() CREATE_DATE, 'Lyven' --插入完成后，对@i进行自加1处理 SET @i=@i+1; --更新@START_DATE的值，在原来的基础上使用DATEADD函数增加一天 SET @START_DATE=CONVERT(CHAR(10),DATEADD(DAY, 1, @START_DATE),120); END END 为了方便阅读，已经在每行代码上方添加了注释。对SQL系统函数不熟悉的可以搜索一下相关函数的用法。 调用存储过程 EXEC PROC_CALENDAR 2021 验证结果 执行完后我们查询一下表CALENDAR_INFO里的数据 SELECT * FROM CALENDAR_INFO 结果： 翻一下日历表，2021-01-01这一天是星期五，说明我们写的日历表是正确的。

After reading the above, do you have any further understanding of how to generate a date dimension table in SQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.