特殊日期加减函数.sql
-
SQL code
-
if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[f_DateADD] ' ) and xtype in (N ' FN ' , N ' IF ' , N ' TF ' )) drop function [ dbo ] . [ f_DateADD ] GO /* --特殊日期加减函数 对于日期指定部分的加减，使用DATEADD函数就可以轻松实现。 在实际的处理中，还有一种比较另类的日期加减处理 就是在指定的日期中，加上（或者减去）多个日期部分 比如将2005年3月11日，加上1年3个月11天2小时。 对于这种日期的加减处理，DATEADD函数的力量就显得有点不够。 本函数实现这样格式的日期字符串加减处理： y-m-d h:m:s.m | -y-m-d h:m:s.m 说明： 要加减的日期字符输入方式与日期字符串相同。日期与时间部分用空格分隔 最前面一个字符如果是减号（-）的话，表示做减法处理，否则做加法处理。 如果日期字符只包含数字，则视为日期字符中，仅包含天的信息。 -- */ /* --调用示例 SELECT dbo.f_DateADD(GETDATE(),'11:10') -- */ CREATE FUNCTION dbo.f_DateADD( @Date datetime , @DateStr varchar ( 23 ) ) RETURNS datetime AS BEGIN DECLARE @bz int , @s varchar ( 12 ), @i int IF @DateStr IS NULL OR @Date IS NULL OR ( CHARINDEX ( ' . ' , @DateStr ) > 0 AND @DateStr NOT LIKE ' %[:]%[:]%.% ' ) RETURN ( NULL ) IF @DateStr = '' RETURN ( @Date ) SELECT @bz = CASE WHEN LEFT ( @DateStr , 1 ) = ' - ' THEN - 1 ELSE 1 END , @DateStr = CASE WHEN LEFT ( @Date , 1 ) = ' - ' THEN STUFF ( RTRIM ( LTRIM ( @DateStr )), 1 , 1 , '' ) ELSE RTRIM ( LTRIM ( @DateStr )) END IF CHARINDEX ( ' ' , @DateStr ) > 1 OR CHARINDEX ( ' - ' , @DateStr ) > 1 OR ( CHARINDEX ( ' . ' , @DateStr ) = 0 AND CHARINDEX ( ' : ' , @DateStr ) = 0 ) BEGIN SELECT @i = CHARINDEX ( ' ' , @DateStr + ' ' ) , @s = REVERSE ( LEFT ( @DateStr , @i - 1 )) + ' - ' , @DateStr = STUFF ( @DateStr , 1 , @i , '' ) , @i = 0 WHILE @s > '' and @i < 3 SELECT @Date = CASE @i WHEN 0 THEN DATEADD ( Day , @bz * REVERSE ( LEFT ( @s , CHARINDEX ( ' - ' , @s ) - 1 )), @Date ) WHEN 1 THEN DATEADD ( Month , @bz * REVERSE ( LEFT ( @s , CHARINDEX ( ' - ' , @s ) - 1 )), @Date ) WHEN 2 THEN DATEADD ( Year , @bz * REVERSE ( LEFT ( @s , CHARINDEX ( ' - ' , @s ) - 1 )), @Date ) END , @s = STUFF ( @s , 1 , CHARINDEX ( ' - ' , @s ), '' ), @i = @i + 1 END IF @DateStr > '' BEGIN IF CHARINDEX ( ' . ' , @DateStr ) > 0 SELECT @Date = DATEADD (Millisecond , @bz * STUFF ( @DateStr , 1 , CHARINDEX ( ' . ' , @DateStr ), '' ), @Date ), @DateStr =LEFT ( @DateStr , CHARINDEX ( ' . ' , @DateStr ) - 1 ) + ' : ' , @i = 0 ELSE SELECT @DateStr = @DateStr + ' : ' , @i = 0 WHILE @DateStr > '' and @i < 3 SELECT @Date = CASE @i WHEN 0 THEN DATEADD (Hour, @bz *LEFT ( @DateStr , CHARINDEX ( ' : ' , @DateStr ) - 1 ), @Date ) WHEN 1 THEN DATEADD (Minute, @bz *LEFT ( @DateStr , CHARINDEX ( ' : ' , @DateStr ) - 1 ), @Date ) WHEN 2 THEN DATEADD (Second, @bz *LEFT ( @DateStr , CHARINDEX ( ' : ' , @DateStr ) - 1 ), @Date ) END , @DateStr = STUFF ( @DateStr , 1 , CHARINDEX ( ' : ' , @DateStr ), '' ), @i = @i + 1 END RETURN ( @Date ) END GO
查询指定日期段内过生日的人员.sql
-
SQL code
-
-- 测试数据 DECLARE @t TABLE (ID int ,Name varchar ( 10 ),Birthday datetime ) INSERT @t SELECT 1 , ' aa ' , ' 1999-01-01 ' UNION ALL SELECT 2 , ' bb ' , ' 1996-02-29 ' UNION ALL SELECT 3 , ' bb ' , ' 1934-03-01 ' UNION ALL SELECT 4 , ' bb ' , ' 1966-04-01 ' UNION ALL SELECT 5 , ' bb ' , ' 1997-05-01 ' UNION ALL SELECT 6 , ' bb ' , ' 1922-11-21 ' UNION ALL SELECT 7 , ' bb ' , ' 1989-12-11 ' DECLARE @dt1 datetime , @dt2 datetime -- 查询 2003-12-05 至 2004-02-28 生日的记录 SELECT @dt1 = ' 2003-12-05 ' , @dt2 = ' 2004-02-28 ' SELECT * FROM @t WHERE DATEADD ( Year , DATEDIFF ( Year ,Birthday, @dt1 ),Birthday) BETWEEN @dt1 AND @dt2 OR DATEADD ( Year , DATEDIFF ( Year ,Birthday, @dt2 ),Birthday) BETWEEN @dt1 AND @dt2 /* --结果 ID Name Birthday ---------------- ---------------- -------------------------- 1 aa 1999-01-01 00:00:00.000 7 bb 1989-12-11 00:00:00.000 -- */ -- 查询 2003-12-05 至 2006-02-28 生日的记录 SET @dt2 = ' 2006-02-28 ' SELECT * FROM @t WHERE DATEADD ( Year , DATEDIFF ( Year ,Birthday, @dt1 ),Birthday) BETWEEN @dt1 AND @dt2 OR DATEADD ( Year , DATEDIFF ( Year ,Birthday, @dt2 ),Birthday) BETWEEN @dt1 AND @dt2 /* --查询结果 ID Name Birthday ---------------- ----------------- -------------------------- 1 aa 1999-01-01 00:00:00.000 2 bb 1996-02-29 00:00:00.000 7 bb 1989-12-11 00:00:00.000 -- */
生成日期列表的函数.sql
-
SQL code
-
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
-
工作日处理函数(标准节假日).sql
-
SQL code
-
if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[f_WorkDay] ' ) and xtype in (N ' FN ' , N ' IF ' , N ' TF ' )) drop function [ dbo ] . [ f_WorkDay ] GO -- 计算两个日期相差的工作天数 CREATE FUNCTION f_WorkDay( @dt_begin datetime , -- 计算的开始日期 @dt_end datetime -- 计算的结束日期 ) RETURNS int AS BEGIN DECLARE @workday int , @i int , @bz bit , @dt datetime IF @dt_begin > @dt_end SELECT @bz = 1 , @dt = @dt_begin , @dt_begin = @dt_end , @dt_end = @dt ELSE SET @bz = 0 SELECT @i = DATEDIFF ( Day , @dt_begin , @dt_end ) + 1 , @workday = @i / 7 * 5 , @dt_begin = DATEADD ( Day , @i / 7 * 7 , @dt_begin ) WHILE @dt_begin <= @dt_end BEGIN SELECT @workday = CASE WHEN ( @@DATEFIRST + DATEPART (Weekday, @dt_begin ) - 1 ) % 7 BETWEEN 1 AND 5 THEN @workday + 1 ELSE @workday END , @dt_begin = @dt_begin + 1 END RETURN ( CASE WHEN @bz = 1 THEN - @workday ELSE @workday END ) END GO /* ================================================================= */ if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[f_WorkDayADD] ' ) and xtype in (N ' FN ' , N ' IF ' , N ' TF ' )) drop function [ dbo ] . [ f_WorkDayADD ] GO -- 在指定日期上,增加指定工作天数后的日期 CREATE FUNCTION f_WorkDayADD( @date datetime , -- 基础日期 @workday int -- 要增加的工作日数 ) RETURNS datetime AS BEGIN DECLARE @bz int -- 增加整周的天数 SELECT @bz = CASE WHEN @workday < 0 THEN - 1 ELSE 1 END , @date = DATEADD (Week, @workday / 5 , @date ) , @workday = @workday % 5 -- 增加不是整周的工作天数 WHILE @workday <> 0 SELECT @date = DATEADD ( Day , @bz , @date ), @workday = CASE WHEN ( @@DATEFIRST + DATEPART (Weekday, @date ) - 1 ) % 7 BETWEEN 1 AND 5 THEN @workday - @bz ELSE @workday END -- 避免处理后的日期停留在非工作日上 WHILE ( @@DATEFIRST + DATEPART (Weekday, @date ) - 1 ) % 7 in ( 0 , 6 ) SET @date = DATEADD ( Day , @bz , @date ) RETURN ( @date ) END
工作日处理函数(自定义节假日).sql
-
SQL code
-
if exists ( select * from dbo.sysobjects where id = object_id (N ' [tb_Holiday] ' ) and OBJECTPROPERTY (id, N ' IsUserTable ' ) = 1 ) drop table [ tb_Holiday ] GO -- 定义节假日表 CREATE TABLE tb_Holiday( HDate smalldatetime primary key clustered , -- 节假日期 Name nvarchar ( 50 ) not null ) -- 假日名称 GO if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[f_WorkDay] ' ) and xtype in (N ' FN ' , N ' IF ' , N ' TF ' )) drop function [ dbo ] . [ f_WorkDay ] GO -- 计算两个日期之间的工作天数 CREATE FUNCTION f_WorkDay( @dt_begin datetime , -- 计算的开始日期 @dt_end datetime -- 计算的结束日期 ) RETURNS int AS BEGIN IF @dt_begin > @dt_end RETURN ( DATEDIFF ( Day , @dt_begin , @dt_end ) + 1 - ( SELECT COUNT ( * ) FROM tb_Holiday WHERE HDate BETWEEN @dt_begin AND @dt_end )) RETURN ( - ( DATEDIFF ( Day , @dt_end , @dt_begin ) + 1 - ( SELECT COUNT ( * ) FROM tb_Holiday WHERE HDate BETWEEN @dt_end AND @dt_begin ))) END GO if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[f_WorkDayADD] ' ) and xtype in (N ' FN ' , N ' IF ' , N ' TF ' )) drop function [ dbo ] . [ f_WorkDayADD ] GO -- 在指定日期上增加工作天数 CREATE FUNCTION f_WorkDayADD( @date datetime , -- 基础日期 @workday int -- 要增加的工作日数 ) RETURNS datetime AS BEGIN IF @workday > 0 WHILE @workday > 0 SELECT @date = @date + @workday , @workday = count ( * ) FROM tb_Holiday WHERE HDate BETWEEN @date AND @date + @workday ELSE WHILE @workday < 0 SELECT @date = @date + @workday , @workday =- count ( * ) FROM tb_Holiday WHERE HDate BETWEEN @date AND @date + @workday RETURN ( @date ) END
计算工作时间的函数.sql
-
SQL code
-
if exists ( select * from dbo.sysobjects where id = object_id (N ' [tb_worktime] ' ) and OBJECTPROPERTY (id, N ' IsUserTable ' ) = 1 ) drop table [ tb_worktime ] GO -- 定义工作时间表 CREATE TABLE tb_worktime( ID int identity ( 1 , 1 ) PRIMARY KEY , -- 序号 time_start smalldatetime , -- 工作的开始时间 time_end smalldatetime , -- 工作的结束时间 worktime AS DATEDIFF (Minute,time_start,time_end) -- 工作时数(分钟) ) GO if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[f_WorkTime] ' ) and xtype in (N ' FN ' , N ' IF ' , N ' TF ' )) drop function [ dbo ] . [ f_WorkTime ] GO -- 计算两个日期之间的工作时间 CREATE FUNCTION f_WorkTime( @date_begin datetime , -- 计算的开始时间 @date_end datetime -- 计算的结束时间 ) RETURNS int AS BEGIN DECLARE @worktime int IF DATEDIFF ( Day , @date_begin , @date_end ) = 0 SELECT @worktime = SUM ( DATEDIFF (Minute, CASE WHEN CONVERT ( VARCHAR , @date_begin , 108 ) > time_start THEN CONVERT ( VARCHAR , @date_begin , 108 ) ELSE time_start END , CASE WHEN CONVERT ( VARCHAR , @date_end , 108 ) < time_end THEN CONVERT ( VARCHAR , @date_end , 108 ) ELSE time_end END )) FROM tb_worktime WHERE time_end > CONVERT ( VARCHAR , @date_begin , 108 ) AND time_start < CONVERT ( VARCHAR , @date_end , 108 ) ELSE SET @worktime = ( SELECT SUM ( CASE WHEN CONVERT ( VARCHAR , @date_begin , 108 ) > time_start THEN DATEDIFF (Minute, CONVERT ( VARCHAR , @date_begin , 108 ),time_end) ELSE worktime END ) FROM tb_worktime WHERE time_end > CONVERT ( VARCHAR , @date_begin , 108 )) + ( SELECT SUM ( CASE WHEN CONVERT ( VARCHAR , @date_end , 108 ) < time_end THEN DATEDIFF (Minute,time_start, CONVERT ( VARCHAR , @date_end , 108 )) ELSE worktime END ) FROM tb_worktime WHERE time_start < CONVERT ( VARCHAR , @date_end , 108 )) + CASE WHEN DATEDIFF ( Day , @date_begin , @date_end ) > 1 THEN ( DATEDIFF ( Day , @date_begin , @date_end ) - 1 ) * ( SELECT SUM (worktime) FROM tb_worktime) ELSE 0 END RETURN ( @worktime ) END
-
1460
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
