8.生成日期列表的函数

桃扇骨 2024-02-19 17:36 88阅读 0赞

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
    
    /*--生成日期列表
    	
    	生成指定年份的工作日/休息日列表
    
    */
    
    /*--调用示例
    
    	--查询 2003 年的工作日列表
    	SELECT * FROM dbo.f_getdate(2003,0)
    	
    	--查询 2003 年的休息日列表
    	SELECT * FROM dbo.f_getdate(2003,1)
    
    	--查询 2003 年全部日期列表
    	SELECT * FROM dbo.f_getdate(2003,NULL)
    --*/
    CREATE FUNCTION dbo.f_getdate(
    @year int,    --要查询的年份
    @bz bit       --@bz=0 查询工作日,@bz=1 查询休息日,@bz IS NULL 查询全部日期
    )RETURNS @re TABLE(id int identity(1,1),Date datetime,Weekday nvarchar(3))
    AS
    BEGIN
    	DECLARE @tb TABLE(ID int IDENTITY(0,1),Date datetime)
    	INSERT INTO @tb(Date) SELECT TOP 366 DATEADD(Year,@YEAR-1900,'1900-1-1')
    	FROM sysobjects a ,sysobjects b
    	UPDATE @tb SET Date=DATEADD(DAY,id,Date)
    	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
    
    
    /*====================================================================*/
    
    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
    
    /*--生成列表
    
    	生成指定日期段的日期列表
    
    --邹建 2005.03(引用请保留此信息)--*/
    
    /*--调用示例
    
    	--查询工作日
    	SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',0)
    	
    	--查询休息日
    	SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',1)
    	
    	--查询全部日期
    	SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',NULL)
    --*/
    
    CREATE FUNCTION dbo.f_getdate(
    @begin_date Datetime,  --要查询的开始日期
    @end_date Datetime,    --要查询的结束日期
    @bz bit                --@bz=0 查询工作日,@bz=1 查询休息日,@bz IS NULL 查询全部日期
    )RETURNS @re TABLE(id int identity(1,1),Date datetime,Weekday nvarchar(3))
    AS
    BEGIN
    	DECLARE @tb TABLE(ID int IDENTITY(0,1),a bit)
    	INSERT INTO @tb(a) SELECT TOP 366 0
    	FROM sysobjects a ,sysobjects b
    	
    	IF @bz=0
    		WHILE @begin_date<=@end_date
    		BEGIN
    			INSERT INTO @re(Date,Weekday)
    			SELECT Date,DATENAME(Weekday,Date)
    			FROM(
    				SELECT Date=DATEADD(Day,ID,@begin_date)
    				FROM @tb				
    			)a WHERE Date<=@end_date
    				AND (DATEPART(Weekday,Date)+@@DATEFIRST-1)%7 BETWEEN 1 AND 5
    			SET @begin_date=DATEADD(Day,366,@begin_date)
    		END
    	ELSE IF @bz=1
    		WHILE @begin_date<=@end_date
    		BEGIN
    			INSERT INTO @re(Date,Weekday)
    			SELECT Date,DATENAME(Weekday,Date)
    			FROM(
    				SELECT Date=DATEADD(Day,ID,@begin_date)
    				FROM @tb				
    			)a WHERE Date<=@end_date
    				AND (DATEPART(Weekday,Date)+@@DATEFIRST-1)%7 in(0,6)
    			SET @begin_date=DATEADD(Day,366,@begin_date)
    		END
    	ELSE
    		WHILE @begin_date<=@end_date
    		BEGIN
    			INSERT INTO @re(Date,Weekday)
    			SELECT Date,DATENAME(Weekday,Date)
    			FROM(
    				SELECT Date=DATEADD(Day,ID,@begin_date)
    				FROM @tb				
    			)a WHERE Date<=@end_date
    			SET @begin_date=DATEADD(Day,366,@begin_date)
    		END
    
    	RETURN
    END
    GO