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.12(引用请保留此信息)--*//*--调用示例 --查询 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
set rowcount 31 select identity(int,0,1) as id into # from sysobjects set rowcount 0declare @t table(Date varchar(10))insert into @t select convert(char(8),dateadd(dd,id,'2006-03-01'),112) from # where datediff(mm,dateadd(dd,id,'2006-03-01'),'2006-03-01')=0select * from @tdrop table #
drop function [dbo].[f_getdate]
GO/*--生成日期列表
生成指定年份的工作日/休息日列表--邹建 2003.12(引用请保留此信息)--*//*--调用示例 --查询 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
select identity(int,0,1) as id into # from sysobjects
set rowcount 0declare @t table(Date varchar(10))insert into @t select convert(char(8),dateadd(dd,id,'2006-03-01'),112) from # where datediff(mm,dateadd(dd,id,'2006-03-01'),'2006-03-01')=0select * from @tdrop table #