MSSQL生成日期列表

-->Title:Generating test data

-->Author:wufeng4552

-->Date :2009-09-28 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,    --要查询年份

@bz bit        --@bz=0 查询工作日,@bz=1 查询休息日,@bz IS NULL 查询全部日期

 )

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,'1900-1-1'))

from master..spt_values where type='P' and number between 0 and 366

DELETE FROM @tb WHERE Date>DATEADD(Year,@YEAR-1900,'1900-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]('2009',0)