讨论[计算工作时间的函数]

MJunnnn 2009-11-08 11:49:12 
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
...全文
194 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
让你望见影子的墙 2009-11-08
  • 打赏
  • 举报
 回复
学习
feixianxxx 2009-11-08
  • 打赏
  • 举报
 回复
邹老大的吧？
ws_hgo 2009-11-08
  • 打赏
  • 举报
 回复
gz
bancxc 2009-11-08
  • 打赏
  • 举报
 回复
先占板凳 再看
SQL77 2009-11-08
  • 打赏
  • 举报
 回复
学习
mingjunwang 2009-11-08
  • 打赏
  • 举报
 回复
特殊日期加减函数
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
mingjunwang 2009-11-08
  • 打赏
  • 举报
 回复
工作日处理函数(标准节假日)
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
mingjunwang 2009-11-08
  • 打赏
  • 举报
 回复
工作日处理函数(自定义节假日)
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
feixianxxx 2009-11-08
  • 打赏
  • 举报
 回复 
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
rucypli 2009-11-08
  • 打赏
  • 举报
 回复
zzzzzz、
计算机函数换算公式法,Ex49 功函数（work function）的计算（一）
前面我们讲完了表面的计算，后面两节我们主要讨论一下功函数的计算。1 功函数的定义首先，我们先看一下功函数(又称功函、逸出功)是指要使一粒电子立即从固体表面中逸出，所必须提供的最小能量(通常以电子伏特为单位)...
计算机中成绩等级排名的函数怎么写,计算机二级excel函数总结（第一篇）
函数共4篇，我们先来看第一篇(第一篇介绍19个函数)写在前面的话计算机等级考试计算机二级中实际考到的函数并不多，希望大家用心去接触，excel函数一直被同学们当作是二级考试中的一只拦路虎，大猫觉得它更像是一只纸...
计算机应用函数count,计算机二级office考试中excel考点：countif函数的使用方法
2019年9月份计算机等级考试还剩不到50天的时间了，参加二级office考试的同学应该都清楚题型的结构组成：选择题(20分)、操作题(80分)，其中大致可以分为word、excel、PPT三部分内容，今天要讲的countif函数就是excel...
1097: 计算平均成绩（函数专题）Python
1097: 计算平均成绩（函数专题） 时间限制: 1 Sec 内存限制: 128 MB 提交: 23548 解决: 11774 [状态] [讨论版] [提交] [命题人:admin] 题目描述 输入某位同学各门课的成绩，输出平均成绩。输入的成绩均为五级制成绩...
1097: 计算平均成绩（函数专题）C语言
1097: 计算平均成绩（函数专题） 时间限制: 1 Sec 内存限制: 128 MB 提交: 23531 解决: 11766 [状态] [讨论版] [提交] [命题人:admin] 题目描述 输入某位同学各门课的成绩，输出平均成绩。输入的成绩均为五级制成绩...
应用实例

27,579

社区成员

68,558

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章