MSSQL generation date list

--> Title: generating test data

--> Author: wufeng4552

--> Date: 08:52:38

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [f_getdate] ') and xtype in (n'fn', n'if', n'tf '))

Drop function [DBO]. [f_getdate]

Go

Create Function [DBO]. [f_getdate]

(

@ Year int, -- the year to be queried

@ BZ bit -- @ Bz = 0 query workday, @ Bz = 1 query rest day, @ BZ is null query all date

)

Returns @ Re table (date datetime, weekday nvarchar (3 ))

As

Begin

Declare @ TB table (ID int, date datetime)

Insert @ TB select number,

Dateadd (day, number, dateadd (year, @ year-1900, '2017-1-1 '))

From master .. spt_values where type = 'p' and number between 0 and 366

Delete from @ TB where date> dateadd (year, @ year-1900, '2017-12-31 ')

If @ Bz = 0

Insert into @ Re (date, weekday)

Select date, datename (weekday, date)

From @ TB

Where (datepart (weekday, date) + @ DATEFIRST-1) % 7 between 1 and 5

Else if @ Bz = 1

Insert into @ Re (date, weekday)

Select date, datename (weekday, date)

From @ TB

Where (datepart (weekday, date) + @ DATEFIRST-1) % 7 in (0, 6)

Else

Insert into @ Re (date, weekday)

Select date, datename (weekday, date)

From @ TB

Return

End

Go

Select * From DBO. [f_getdate] ('20140901', 0)