How to use Spt_Values to solve the continuous date problem in SQL

This article focuses on "how to use Spt_Values to solve the continuous date problem in SQL". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to use Spt_Values to solve the continuous date problem in SQL.

What is spt_values?

Spt_values is a new system table added by SQL Server, which is full of enumerated data. We can view the data in it with the following query statement

Select * from master..spt_values

(because the table belongs under the system database master, it is common to add the library name master before the table name)

The result is:

(more records, only part of the records are intercepted)

Spt_values continuous recording

But usually we use Type='P' data records, which are a set of consecutive integers starting at 0 and ending at 2047, as follows:

Select * from master..spt_values where type='P'

The result is:

(more records, only part of the records are intercepted)

What we often use is the number column, through which we can generate many consecutive records, including consecutive dates, such as 24 hours a day, every day of each month, 12 months of each year, and so on.

Generate 24 hours a day we only need to specify the start and end times to generate consecutive hours for that period, here from 0 to 23:00.

SELECT SUBSTRING (CONVERT (CHAR (32), DATEADD (HH,number,CONCAT ('2021-01-05), 1) AS GroupDay FROM master..spt_values WHERE TYPE =' P' AND DATEDIFF (HH,DATEADD (HH,number,CONCAT ('2021-01-05)), CONCAT (' 2021-01-05)) > = 0

(hint: you can swipe the code left and right) the result is:

(there are 24 complete records, only the first few are intercepted here)

To generate each day of the month, we only need to specify the start and end dates to generate the consecutive days of that date period, here from January 1 to January 31.

SELECT CONVERT (NVARCHAR (10), DATEADD (DAY, number, '2021-01-01'), AS GroupDay FROM master..spt_values WHERE TYPE ='P' AND number