Several ways SQL generates a list of times per day of the year

Worked for several years, has not written a blog, ready to pick up ... The following script applies to the environment: SQL SERVER (starting with 2012) 1, Build sequence:/*1-1: Use cross-linking to recommend the following notation */

SELECT/*2012 started with the offset syntax and is no longer recommended for top N*/s1.i+s2.i+s3.i+S4.i+S5.i+S6.i+S7.i+S8.i+S9.i asseq from(SELECTI= 0 UNION  All SELECTI= 1) S1 Cross JOIN(SELECT 0  asIUNION  All SELECT 2  asi) S2 Cross JOIN(SELECT 0  asIUNION  All SELECT 4  asi) S3 Cross JOIN(SELECT 0  asIUNION  All SELECT 8  asi) S4 Cross JOIN(SELECT 0  asIUNION  All SELECT  -  asi) S5 Cross JOIN(SELECT 0  asIUNION  All SELECT  +  asi) S6 Cross JOIN(SELECT 0  asIUNION  All SELECT  -  asi) S7 Cross JOIN(SELECT 0  asIUNION  All SELECT  -  asi) S8 Cross JOIN(SELECT 0  asIUNION  All SELECT  the  asi) S9ORDER  bySeqoffset0ROWSFETCH NEXT 366ROWS only

/* 1-2: If the number of system table columns is insufficient or too large, the result or performance will be affected */

SELECTRow_number () Over(ORDER  byA1.object_id)- 1  asseq fromSys.all_columns A1 with(NOLOCK), Sys.all_columns A2 with(NOLOCK)ORDER  bySeqoffset0ROWSFETCH NEXT 366ROWS only

/* 1-3: Looping, deprecated notation */

DECLARE @i INTDECLARE @seq TABLE(SeqINT  not NULL)SET @i=0 while @i < 366BEGININSERT  into @seq(seq)VALUES(@i)SET @i+=1ENDSELECT *  from @seq

2. Based on any of the above sequences, use DATEADD (Day,seq, @DstDate) to generate a time list for each day of the year. 3, expand: How to use the above ideas in MySQL, in the premise of not writing stored procedures or functions, the use of SQL statements to split a string into an array? Click here to transfer to view answers >>>

Several ways SQL generates a list of times per day of the year