MSSQL 生成日期列表代码_mssql数据库教程-查字典教程网
MSSQL 生成日期列表代码
MSSQL 生成日期列表代码
发布时间:2016-12-29 来源:查字典编辑
摘要:复制代码代码如下:ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[...

复制代码 代码如下:

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)

相关阅读
推荐文章
猜你喜欢
附近的人在看
推荐阅读
拓展阅读
  • 大家都在看
  • 小编推荐
  • 猜你喜欢
  • 最新mssql数据库学习
    热门mssql数据库学习
    编程开发子分类