mssql日期:MSSQL生成日期列表

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)

延伸阅读

• 2010-12-9-- mssql日期,MSSQL 生成日期列表代码