（转）数据库中关于时间日期函数
--A. 测试 datetime 精度问题
DECLARE @t TABLE(date char(21))
INSERT @t SELECT '1900-1-1 00:00:00.000'
INSERT @t SELECT '1900-1-1 00:00:00.001'
INSERT @t SELECT '1900-1-1 00:00:00.009'
INSERT @t SELECT '1900-1-1 00:00:00.002'
INSERT @t SELECT '1900-1-1 00:00:00.003'
INSERT @t SELECT '1900-1-1 00:00:00.004'
INSERT @t SELECT '1900-1-1 00:00:00.005'
INSERT @t SELECT '1900-1-1 00:00:00.006'
INSERT @t SELECT '1900-1-1 00:00:00.007'
INSERT @t SELECT '1900-1-1 00:00:00.008'
SELECT date,转换后的日期=CAST(date as datetime) FROM @t
/*--结果
date 转换后的日期
--------------------- --------------------------
1900-1-1 00:00:00.000 1900-01-01 00:00:00.000
1900-1-1 00:00:00.001 1900-01-01 00:00:00.000
1900-1-1 00:00:00.009 1900-01-01 00:00:00.010
1900-1-1 00:00:00.002 1900-01-01 00:00:00.003
1900-1-1 00:00:00.003 1900-01-01 00:00:00.003
1900-1-1 00:00:00.004 1900-01-01 00:00:00.003
1900-1-1 00:00:00.005 1900-01-01 00:00:00.007
1900-1-1 00:00:00.006 1900-01-01 00:00:00.007
1900-1-1 00:00:00.007 1900-01-01 00:00:00.007
1900-1-1 00:00:00.008 1900-01-01 00:00:00.007
（所影响的行数为 10 行）
--*/
GO
--B. 对于 datetime 类型的纯日期和时间的十六进制表示
DECLARE @dt datetime
--单纯的日期
SET @dt='1900-1-2'
SELECT CAST(@dt as binary(8))
--结果: 0x0000000100000000
--单纯的时间
SET @dt='00:00:01'
SELECT CAST(@dt as binary(8))
--结果: 0x000000000000012C
GO
--C. 对于 smalldatetime 类型的纯日期和时间的十六进制表示
DECLARE @dt smalldatetime
--单纯的日期
SET @dt='1900-1-2'
SELECT CAST(@dt as binary(4))
--结果: 0x00010000
--单纯的时间
SET @dt='00:10'
SELECT CAST(@dt as binary(4))
--结果: 0x0000000A
2、CONVERT在日期转换中的使用示例
--字符转换为日期时,Style的使用
--1. Style=101时,表示日期字符串为:mm/dd/yyyy格式
SELECT CONVERT(datetime,'11/1/2003',101)
--结果:2003-11-01 00:00:00.000
--2. Style=101时,表示日期字符串为:dd/mm/yyyy格式
SELECT CONVERT(datetime,'11/1/2003',103)
--结果:2003-01-11 00:00:00.000
/*== 日期转换为字符串 ==*/
DECLARE @dt datetime
SET @dt='2003-1-11'
--1. Style=101时,表示将日期转换为:mm/dd/yyyy 格式
SELECT CONVERT(varchar,@dt,101)
--结果:01/11/2003
--2. Style=103时,表示将日期转换为:dd/mm/yyyy 格式
SELECT CONVERT(varchar,@dt,103)
--结果:11/01/2003
/*== 这是很多人经常犯的错误,对非日期型转换使用日期的style样式 ==*/
SELECT CONVERT(varchar,'2003-1-11',101)
--结果:2003-1-11
3、SET DATEFORMAT对日期处理的影响
--1.
/*--说明
SET DATEFORMAT设置对使用CONVERT把字符型日期转换为日期的处理也具有影响
但不影响明确指定了style的CONVERT处理。
--*/
--示例 ，在下面的示例中，第一个CONVERT转换未指定style，转换的结果受SET DATAFORMAT的影响，第二个CONVERT转换指定了style，转换结果受style的影响。
--设置输入日期顺序为 日/月/年
SET DATEFORMAT DMY
--不指定Style参数的CONVERT转换将受到SET DATEFORMAT的影响
SELECT CONVERT(datetime,'2-1-2005')
--结果: 2005-01-02 00:00:00.000
--指定Style参数的CONVERT转换不受SET DATEFORMAT的影响
SELECT CONVERT(datetime,'2-1-2005',101)
--结果: 2005-02-01 00:00:00.000
GO
--2.
/*--说明
如果输入的日期包含了世纪部分，则对日期进行解释处理时
年份的解释不受SET DATEFORMAT设置的影响。
--*/
--示例，在下面的代码中，同样的SET DATEFORMAT设置，输入日期的世纪部分与不输入日期的世纪部分，解释的日期结果不同。
DECLARE @dt datetime
--设置SET DATEFORMAT为:月日年
SET DATEFORMAT MDY
--输入的日期中指定世纪部分
SET @dt='01-2002-03'
SELECT @dt
--结果: 2002-01-03 00:00:00.000
--输入的日期中不指定世纪部分
SET @dt='01-02-03'
SELECT @dt
--结果: 2003-01-02 00:00:00.000
GO
--3.
/*--说明
如果输入的日期不包含日期分隔符，那么SQL Server在对日期进行解释时
将忽略SET DATEFORMAT的设置。
--*/
--示例，在下面的代码中，不包含日期分隔符的字符日期，在不同的SET DATEFORMAT设置下，其解释的结果是一样的。
DECLARE @dt datetime
--设置SET DATEFORMAT为:月日年
SET DATEFORMAT MDY
SET @dt='010203'
SELECT @dt
--结果: 2001-02-03 00:00:00.000
--设置SET DATEFORMAT为:日月年
SET DATEFORMAT DMY
SET @dt='010203'
SELECT @dt
--结果: 2001-02-03 00:00:00.000
--输入的日期中包含日期分隔符
SET @dt='01-02-03'
SELECT @dt
--结果: 2003-02-01 00:00:00.000
4、SET LANGUAGE对日期处理的影响示例
--以下示例演示了在不同的语言环境(SET LANGUAGE)下，DATENAME与CONVERT函数的不同结果。
USE master
--设置会话的语言环境为: English
SET LANGUAGE N'English'
SELECT
DATENAME(Month,GETDATE()) AS [Month],
DATENAME(Weekday,GETDATE()) AS [Weekday],
CONVERT(varchar,GETDATE(),109) AS [CONVERT]
/*--结果:
Month Weekday CONVERT
------------- -------------- -------------------------------
March Tuesday Mar 15 2005 8:59PM
--*/
--设置会话的语言环境为: 简体中文
SET LANGUAGE N'简体中文'
SELECT
DATENAME(Month,GETDATE()) AS [Month],
DATENAME(Weekday,GETDATE()) AS [Weekday],
CONVERT(varchar,GETDATE(),109) AS [CONVERT]
/*--结果
Month Weekday CONVERT
------------- --------------- -----------------------------------------
05 星期四 05 19 2005 2:49:20:607PM
--*/
5、日期格式化处理
DECLARE @dt datetime
SET @dt=GETDATE()
--1．短日期格式：yyyy-m-d
SELECT REPLACE(CONVERT(varchar(10),@dt,120),N'-0','-')
--2．长日期格式：yyyy年mm月dd日
--A. 方法1
SELECT STUFF(STUFF(CONVERT(char(8),@dt,112),5,0,N'年'),8,0,N'月')+N'日'
--B. 方法2
SELECT DATENAME(Year,@dt)+N'年'+DATENAME(Month,@dt)+N'月'+DATENAME(Day,@dt)+N'日'
--3．长日期格式：yyyy年m月d日
SELECT DATENAME(Year,@dt)+N'年'+CAST(DATEPART(Month,@dt) AS varchar)+N'月'+DATENAME(Day,@dt)+N'日'
--4.完整日期+时间格式：yyyy-mm-dd hh:mi:ss:mmm
SELECT CONVERT(char(11),@dt,120)+CONVERT(char(12),@dt,114)
6、日期推算处理
DECLARE @dt datetime
SET @dt=GETDATE()
DECLARE @number int
SET @number=3
--1．指定日期该年的第一天或最后一天
--A. 年的第一天
SELECT CONVERT(char(5),@dt,120)+'1-1'
--B. 年的最后一天
SELECT CONVERT(char(5),@dt,120)+'12-31'
--2．指定日期所在季度的第一天或最后一天
--A. 季度的第一天
SELECT CONVERT(datetime,
CONVERT(char(8),
DATEADD(Month,
DATEPART(Quarter,@dt)*3-Month(@dt)-2,
@dt),
120)+'1')
--B. 季度的最后一天（CASE判断法）
SELECT CONVERT(datetime,
CONVERT(char(8),
DATEADD(Month,
DATEPART(Quarter,@dt)*3-Month(@dt),
@dt),
120)
+CASE WHEN DATEPART(Quarter,@dt) in(1,4)
THEN '31'ELSE '30' END)
--C. 季度的最后一天（直接推算法）
SELECT DATEADD(Day,-1,
CONVERT(char(8),
DATEADD(Month,
1+DATEPART(Quarter,@dt)*3-Month(@dt),
@dt),
120)+'1')
--3．指定日期所在月份的第一天或最后一天
--A. 月的第一天
SELECT CONVERT(datetime,CONVERT(char(8),@dt,120)+'1')
--B. 月的最后一天
SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+'1')
--C. 月的最后一天（容易使用的错误方法）
SELECT DATEADD(Month,1,DATEADD(Day,-DAY(@dt),@dt))
--4．指定日期所在周的任意一天
SELECT DATEADD(Day,@number-DATEPART(Weekday,@dt),@dt)
--5．指定日期所在周的任意星期几
--A. 星期天做为一周的第1天
SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt)
--B. 星期一做为一周的第1天
SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-2)%7-1,@dt)
7、特殊日期加减函数
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
8、查询指定日期段内过生日的人员
--测试数据
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
--*/
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
10、工作日处理函数(标准节假日)
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
11、工作日处理函数(自定义节假日)
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
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
13、复杂年月处理
--定义基本数字表
declare @T1 table(代码 int,名称 varchar(10),参加时间 datetime,终止时间 datetime)
insert into @T1
select 12,'单位1','2003/04/01','2004/05/01'
union all select 22,'单位2','2001/02/01','2003/02/01'
union all select 42,'单位3','2000/04/01','2003/05/01'
union all select 25,'单位5','2003/04/01','2003/05/01'
--定义年表
declare @NB table(代码 int,名称 varchar(10),年份 int)
insert into @NB
select 12,'单位1',2003
union all select 12,'单位1',2004
union all select 22,'单位2',2001
union all select 22,'单位2',2002
union all select 22,'单位2',2003
--定义月表
declare @YB table(代码 int,名称 varchar(10),年份 int,月份 varchar(2))
insert into @YB
select 12,'单位1',2003,'04'
union all select 22,'单位2',2001,'01'
union all select 22,'单位2',2001,'12'
--为年表+月表数据处理准备临时表
select top 8246 y=identity(int,1753,1)
into #tby from
(select id from syscolumns) a,
(select id from syscolumns) b,
(select id from syscolumns) c
--为月表数据处理准备临时表
select top 12 m=identity(int,1,1)
into #tbm from syscolumns
/*--数据处理--*/
--年表数据处理
select a.*
from(
select a.代码,a.名称,年份=b.y
from @T1 a,#tby b
where b.y between year(参加时间) and year(终止时间)
) a left join @NB b on a.代码=b.代码 and a.年份=b.年份
where b.代码 is null
--月表数据处理
select a.*
from(
select a.代码,a.名称,年份=b.y,月份=right('00'+cast(c.m as varchar),2)
from @T1 a,#tby b,#tbm c
where b.y*100+c.m between convert(varchar(6),参加时间,112)
and convert(varchar(6),终止时间,112)
) a left join @YB b on a.代码=b.代码 and a.年份=b.年份 and a.月份=b.月份
where b.代码 is null
order by a.代码,a.名称,a.年份,a.月份
--删除数据处理临时表
drop table #tby,#tbm
14、 交叉表
--示例
--示例数据
create table tb(ID int,Time datetime)
insert tb select 1,'2005/01/24 16:20'
union all select 2,'2005/01/23 22:45'
union all select 3,'2005/01/23 0:30'
union all select 4,'2005/01/21 4:28'
union all select 5,'2005/01/20 13:22'
union all select 6,'2005/01/19 20:30'
union all select 7,'2005/01/19 18:23'
union all select 8,'2005/01/18 9:14'
union all select 9,'2005/01/18 18:04'
go
--查询处理：
select case when grouping(b.Time)=1 then 'Total' else b.Time end,
[Mon]=sum(case a.week when 1 then 1 else 0 end),
[Tue]=sum(case a.week when 2 then 1 else 0 end),
[Wed]=sum(case a.week when 3 then 1 else 0 end),
[Thu]=sum(case a.week when 4 then 1 else 0 end),
[Fri]=sum(case a.week when 5 then 1 else 0 end),
[Sat]=sum(case a.week when 6 then 1 else 0 end),
[Sun]=sum(case a.week when 0 then 1 else 0 end),
[Total]=count(a.week)
from(
select Time=convert(char(5),dateadd(hour,-1,Time),108)
--时间交界点是1am,所以减1小时，避免进行跨天处理
,week=(@@datefirst+datepart(weekday,Time)-1)%7
--考虑@@datefirst对datepart的影响
from tb
)a right join(
select id=1,a='16:00',b='19:59',Time='[5pm - 9pm)' union all
select id=2,a='20:00',b='23:59',Time='[9pm - 1am)' union all
select id=3,a='00:00',b='02:59',Time='[1am - 4am)' union all
select id=4,a='03:00',b='07:29',Time='[4am - 8:30am)' union all
select id=5,a='07:30',b='11:59',Time='[8:30am - 1pm)' union all
select id=6,a='12:00',b='15:59',Time='[1pm - 5pm)'
)b on a.Time>=b.a and a.Time<b.b
group by b.id,b.Time with rollup
having grouping(b.Time)=0 or grouping(b.id)=1
go
--删除测试
drop table tb
/*--测试结果
Mon Tue Wed Thu Fri Sat Sun Total
-------------- ----- ----- ----- ----- ----- ------ ---- -------
[5pm - 9pm) 0 1 2 0 0 0 0 3
[9pm - 1am) 0 0 0 0 0 0 2 2
[1am - 4am) 0 0 0 0 0 0 0 0
[4am - 8:30am) 0 0 0 0 1 0 0 1
[8:30am - 1pm) 0 1 0 0 0 0 0 1
[1pm - 5pm) 1 0 0 1 0 0 0 2
Total 1 2 2 1 1 0 2 9
（所影响的行数为 7 行）
--*/
15、任意两个时间之间的星期几的次数-横
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_weekdaycount]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_weekdaycount]
GO
/*--计算任意两个时间之间的星期几的次数(横向显示)
本方法直接判断 @@datefirst 做对应处理
不受 sp_language 及 set datefirst 的影响
--邹建 2004.08(引用请保留此信息)--*/
/*--调用示例
select * from f_weekdaycount('2004-9-01','2004-9-02')
--*/
create function f_weekdaycount(
@dt_begin datetime,
@dt_end datetime
)returns table
as
return(
select 跨周数
,周一=case a
when -1 then case when 1 between b and c then 1 else 0 end
when 0 then case when b<=1 then 1 else 0 end
+case when c>=1 then 1 else 0 end
else a+case when b<=1 then 1 else 0 end
+case when c>=1 then 1 else 0 end
end
,周二=case a
when -1 then case when 2 between b and c then 1 else 0 end
when 0 then case when b<=2 then 1 else 0 end
+case when c>=2 then 1 else 0 end
else a+case when b<=2 then 1 else 0 end
+case when c>=2 then 1 else 0 end
end
,周三=case a
when -1 then case when 3 between b and c then 1 else 0 end
when 0 then case when b<=3 then 1 else 0 end
+case when c>=3 then 1 else 0 end
else a+case when b<=3 then 1 else 0 end
+case when c>=3 then 1 else 0 end
end
,周四=case a
when -1 then case when 4 between b and c then 1 else 0 end
when 0 then case when b<=4 then 1 else 0 end
+case when c>=4 then 1 else 0 end
else a+case when b<=4 then 1 else 0 end
+case when c>=4 then 1 else 0 end
end
,周五=case a
when -1 then case when 5 between b and c then 1 else 0 end
when 0 then case when b<=5 then 1 else 0 end
+case when c>=5 then 1 else 0 end
else a+case when b<=5 then 1 else 0 end
+case when c>=5 then 1 else 0 end
end
,周六=case a
when -1 then case when 6 between b and c then 1 else 0 end
when 0 then case when b<=6 then 1 else 0 end
+case when c>=6 then 1 else 0 end
else a+case when b<=6 then 1 else 0 end
+case when c>=6 then 1 else 0 end
end
,周日=case a
when -1 then case when 0 between b and c then 1 else 0 end
when 0 then case when b<=0 then 1 else 0 end
+case when c>=0 then 1 else 0 end
else a+case when b<=0 then 1 else 0 end
+case when c>=0 then 1 else 0 end
end
from(
select 跨周数=case when @dt_begin<@dt_end
then (datediff(day,@dt_begin,@dt_end)+7)/7
else (datediff(day,@dt_end,@dt_begin)+7)/7 end
,a=case when @dt_begin<@dt_end
then datediff(week,@dt_begin,@dt_end)-1
else datediff(week,@dt_end,@dt_begin)-1 end
,b=case when @dt_begin<@dt_end
then (@@datefirst+datepart(weekday,@dt_begin)-1)%7
else (@@datefirst+datepart(weekday,@dt_end)-1)%7 end
,c=case when @dt_begin<@dt_end
then (@@datefirst+datepart(weekday,@dt_end)-1)%7
else (@@datefirst+datepart(weekday,@dt_begin)-1)%7 end)a
)
go
16、任意两个时间之间的星期几的次数-纵
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_weekdaycount]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_weekdaycount]
GO
/*--计算任意两个时间之间的星期几的次数(纵向显示)
本方法直接判断 @@datefirst 做对应处理
不受 sp_language 及 set datefirst 的影响
--邹建 2004.08(引用请保留此信息)--*/
/*--调用示例
select * from f_weekdaycount('2004-8-02','2004-8-8')
--*/
create function f_weekdaycount(
@dt_begin datetime,
@dt_end datetime
)returns table
as
return(
select 项目='跨周数'
,值=case when @dt_begin<@dt_end
then (datediff(day,@dt_begin,@dt_end)+7)/7
else (datediff(day,@dt_end,@dt_begin)+7)/7 end
union all
select a.a,case b.a
when -1 then case when a.b between b.b and b.c then 1 else 0 end
when 0 then case when b.b<=a.b then 1 else 0 end
+case when b.c>=a.b then 1 else 0 end
else b.a+case when b.b<=a.b then 1 else 0 end
+case when b.c>=a.b then 1 else 0 end
end
from(select a='星期一',b=1
union all select '星期二',2 union all select '星期三',3
union all select '星期四',4 union all select '星期五',5
union all select '星期六',6 union all select '星期日',0
)a,(select a=case when @dt_begin<@dt_end
then datediff(week,@dt_begin,@dt_end)-1
else datediff(week,@dt_end,@dt_begin)-1 end
,b=case when @dt_begin<@dt_end
then (@@datefirst+datepart(weekday,@dt_begin)-1)%7
else (@@datefirst+datepart(weekday,@dt_end)-1)%7 end
,c=case when @dt_begin<@dt_end
then (@@datefirst+datepart(weekday,@dt_end)-1)%7
else (@@datefirst+datepart(weekday,@dt_begin)-1)%7 end)b
)
go
17、统计--交叉表+日期+优先
--交叉表,根据优先级取数据,日期处理
create table tb(qid int,rid nvarchar(4),tagname nvarchar(10),starttime smalldatetime,endtime smalldatetime,startweekday int,endweekday int,startdate smalldatetime,enddate smalldatetime,d int)
insert tb select 1,'A1','未订','08:00','09:00',1 ,5 ,null ,null ,1
union all select 1,'A1','未订','09:00','10:00',1 ,5 ,null ,null ,1
union all select 1,'A1','未订','10:00','11:00',1 ,5 ,null ,null ,1
union all select 1,'A1','装修','08:00','09:00',null,null,'2005-1-18','2005-1-19',2
--union all select 1,'A1','装修','09:00','10:00',null,null,'2005-1-18','2005-1-19',2
union all select 1,'A1','装修','10:00','11:00',null,null,'2005-1-18','2005-1-19',2
union all select 1,'A2','未订','08:00','09:00',1 ,5 ,null ,null ,1
union all select 1,'A2','未订','09:00','10:00',1 ,5 ,null ,null ,1
union all select 1,'A2','未订','10:00','11:00',1 ,5 ,null ,null ,1
--union all select 1,'A2','装修','08:00','09:00',null,null,'2005-1-18','2005-1-19',2
union all select 1,'A2','装修','09:00','10:00',null,null,'2005-1-18','2005-1-19',2
--union all select 1,'A2','装修','10:00','11:00',null,null,'2005-1-18','2005-1-19',2
go
/*--楼主这个问题要考虑几个方面
1. 取星期时,set datefirst 的影响
2. 优先级问题
3. qid,rid 应该是未知的(动态变化的)
--*/
--实现的存储过程如下
create proc p_qry
@date smalldatetime --要查询的日期
as
set nocount on
declare @week int,@s nvarchar(4000)
--格式化日期和得到星期
select @date=convert(char(10),@date,120)
,@week=(@@datefirst+datepart(weekday,@date)-1)%7
,@s=''
select id=identity(int),* into #t
from(
select top 100 percent
qid,rid,tagname,
starttime=convert(char(5),starttime,108),
endtime=convert(char(5),endtime,108)
from tb
where (@week between startweekday and endweekday)
or(@date between startdate and enddate)
order by qid,rid,starttime,d desc)a
select @s=@s+N',['+rtrim(rid)
+N']=max(case when qid='+rtrim(qid)
+N' and rid=N'''+rtrim(rid)
+N''' then tagname else N'''' end)'
from #t group by qid,rid
exec('
select starttime,endtime'+@s+'
from #t a
where not exists(
select * from #t
where qid=a.qid and rid=a.rid
and starttime=a.starttime
and endtime=a.endtime
and id<a.id)
group by starttime,endtime')
go
--调用
exec p_qry '2005-1-17'
exec p_qry '2005-1-18'
go
--删除测试
drop table tb
drop proc p_qry
/*--测试结果
starttime endtime A1 A2
--------- ------- ---------- ----------
08:00 09:00 未订 未订
09:00 10:00 未订 未订
10:00 11:00 未订 未订
starttime endtime A1 A2
--------- ------- ---------- ----------
08:00 09:00 装修 未订
09:00 10:00 未订 装修
10:00 11:00 装修 未订
--*/
DECLARE @t TABLE(date char(21))INSERT @t SELECT '1900-1-1 00:00:00.000'INSERT @t SELECT '1900-1-1 00:00:00.001'INSERT @t SELECT '1900-1-1 00:00:00.009'INSERT @t SELECT '1900-1-1 00:00:00.002'INSERT @t SELECT '1900-1-1 00:00:00.003'INSERT @t SELECT '1900-1-1 00:00:00.004'INSERT @t SELECT '1900-1-1 00:00:00.005'INSERT @t SELECT '1900-1-1 00:00:00.006'INSERT @t SELECT '1900-1-1 00:00:00.007'INSERT @t SELECT '1900-1-1 00:00:00.008'SELECT date,转换后的日期=CAST(date as datetime) FROM @t/*--结果
date 转换后的日期--------------------- --------------------------1900-1-1 00:00:00.000 1900-01-01 00:00:00.0001900-1-1 00:00:00.001 1900-01-01 00:00:00.0001900-1-1 00:00:00.009 1900-01-01 00:00:00.0101900-1-1 00:00:00.002 1900-01-01 00:00:00.0031900-1-1 00:00:00.003 1900-01-01 00:00:00.0031900-1-1 00:00:00.004 1900-01-01 00:00:00.0031900-1-1 00:00:00.005 1900-01-01 00:00:00.0071900-1-1 00:00:00.006 1900-01-01 00:00:00.0071900-1-1 00:00:00.007 1900-01-01 00:00:00.0071900-1-1 00:00:00.008 1900-01-01 00:00:00.007（所影响的行数为 10 行）--*/GO--B. 对于 datetime 类型的纯日期和时间的十六进制表示DECLARE @dt datetime--单纯的日期SET @dt='1900-1-2'SELECT CAST(@dt as binary(8))--结果: 0x0000000100000000--单纯的时间SET @dt='00:00:01'SELECT CAST(@dt as binary(8))--结果: 0x000000000000012CGO--C. 对于 smalldatetime 类型的纯日期和时间的十六进制表示DECLARE @dt smalldatetime--单纯的日期SET @dt='1900-1-2'SELECT CAST(@dt as binary(4))--结果: 0x00010000--单纯的时间SET @dt='00:10'SELECT CAST(@dt as binary(4))--结果: 0x0000000A2、CONVERT在日期转换中的使用示例
--字符转换为日期时,Style的使用--1. Style=101时,表示日期字符串为:mm/dd/yyyy格式SELECT CONVERT(datetime,'11/1/2003',101)--结果:2003-11-01 00:00:00.000--2. Style=101时,表示日期字符串为:dd/mm/yyyy格式SELECT CONVERT(datetime,'11/1/2003',103)--结果:2003-01-11 00:00:00.000/*== 日期转换为字符串 ==*/DECLARE @dt datetimeSET @dt='2003-1-11'--1. Style=101时,表示将日期转换为:mm/dd/yyyy 格式SELECT CONVERT(varchar,@dt,101)--结果:01/11/2003--2. Style=103时,表示将日期转换为:dd/mm/yyyy 格式SELECT CONVERT(varchar,@dt,103)--结果:11/01/2003/*== 这是很多人经常犯的错误,对非日期型转换使用日期的style样式 ==*/SELECT CONVERT(varchar,'2003-1-11',101)--结果:2003-1-113、SET DATEFORMAT对日期处理的影响
--1./*--说明 SET DATEFORMAT设置对使用CONVERT把字符型日期转换为日期的处理也具有影响 但不影响明确指定了style的CONVERT处理。--*/--示例 ，在下面的示例中，第一个CONVERT转换未指定style，转换的结果受SET DATAFORMAT的影响，第二个CONVERT转换指定了style，转换结果受style的影响。--设置输入日期顺序为 日/月/年SET DATEFORMAT DMY--不指定Style参数的CONVERT转换将受到SET DATEFORMAT的影响SELECT CONVERT(datetime,'2-1-2005')--结果: 2005-01-02 00:00:00.000--指定Style参数的CONVERT转换不受SET DATEFORMAT的影响SELECT CONVERT(datetime,'2-1-2005',101)--结果: 2005-02-01 00:00:00.000GO--2./*--说明 如果输入的日期包含了世纪部分，则对日期进行解释处理时 年份的解释不受SET DATEFORMAT设置的影响。--*/--示例，在下面的代码中，同样的SET DATEFORMAT设置，输入日期的世纪部分与不输入日期的世纪部分，解释的日期结果不同。DECLARE @dt datetime--设置SET DATEFORMAT为:月日年SET DATEFORMAT MDY--输入的日期中指定世纪部分SET @dt='01-2002-03'SELECT @dt--结果: 2002-01-03 00:00:00.000--输入的日期中不指定世纪部分SET @dt='01-02-03'SELECT @dt--结果: 2003-01-02 00:00:00.000GO--3./*--说明 如果输入的日期不包含日期分隔符，那么SQL Server在对日期进行解释时 将忽略SET DATEFORMAT的设置。--*/--示例，在下面的代码中，不包含日期分隔符的字符日期，在不同的SET DATEFORMAT设置下，其解释的结果是一样的。DECLARE @dt datetime--设置SET DATEFORMAT为:月日年SET DATEFORMAT MDYSET @dt='010203'SELECT @dt--结果: 2001-02-03 00:00:00.000--设置SET DATEFORMAT为:日月年SET DATEFORMAT DMYSET @dt='010203'SELECT @dt--结果: 2001-02-03 00:00:00.000--输入的日期中包含日期分隔符SET @dt='01-02-03'SELECT @dt--结果: 2003-02-01 00:00:00.0004、SET LANGUAGE对日期处理的影响示例
--以下示例演示了在不同的语言环境(SET LANGUAGE)下，DATENAME与CONVERT函数的不同结果。USE master--设置会话的语言环境为: EnglishSET LANGUAGE N'English'SELECT DATENAME(Month,GETDATE()) AS [Month], DATENAME(Weekday,GETDATE()) AS [Weekday], CONVERT(varchar,GETDATE(),109) AS [CONVERT]/*--结果:Month Weekday CONVERT------------- -------------- -------------------------------March Tuesday Mar 15 2005 8:59PM--*/--设置会话的语言环境为: 简体中文SET LANGUAGE N'简体中文'SELECT DATENAME(Month,GETDATE()) AS [Month], DATENAME(Weekday,GETDATE()) AS [Weekday], CONVERT(varchar,GETDATE(),109) AS [CONVERT]/*--结果Month Weekday CONVERT------------- --------------- -----------------------------------------05 星期四 05 19 2005 2:49:20:607PM--*/5、日期格式化处理
DECLARE @dt datetimeSET @dt=GETDATE()--1．短日期格式：yyyy-m-dSELECT REPLACE(CONVERT(varchar(10),@dt,120),N'-0','-')--2．长日期格式：yyyy年mm月dd日 --A. 方法1 SELECT STUFF(STUFF(CONVERT(char(8),@dt,112),5,0,N'年'),8,0,N'月')+N'日'--B. 方法2 SELECT DATENAME(Year,@dt)+N'年'+DATENAME(Month,@dt)+N'月'+DATENAME(Day,@dt)+N'日'--3．长日期格式：yyyy年m月d日SELECT DATENAME(Year,@dt)+N'年'+CAST(DATEPART(Month,@dt) AS varchar)+N'月'+DATENAME(Day,@dt)+N'日'--4.完整日期+时间格式：yyyy-mm-dd hh:mi:ss:mmmSELECT CONVERT(char(11),@dt,120)+CONVERT(char(12),@dt,114)6、日期推算处理
DECLARE @dt datetimeSET @dt=GETDATE()DECLARE @number intSET @number=3--1．指定日期该年的第一天或最后一天--A. 年的第一天SELECT CONVERT(char(5),@dt,120)+'1-1'--B. 年的最后一天SELECT CONVERT(char(5),@dt,120)+'12-31'--2．指定日期所在季度的第一天或最后一天--A. 季度的第一天SELECT CONVERT(datetime, CONVERT(char(8), DATEADD(Month, DATEPART(Quarter,@dt)*3-Month(@dt)-2, @dt), 120)+'1')--B. 季度的最后一天（CASE判断法）SELECT CONVERT(datetime, CONVERT(char(8), DATEADD(Month, DATEPART(Quarter,@dt)*3-Month(@dt), @dt), 120) +CASE WHEN DATEPART(Quarter,@dt) in(1,4) THEN '31'ELSE '30' END)--C. 季度的最后一天（直接推算法）SELECT DATEADD(Day,-1, CONVERT(char(8), DATEADD(Month, 1+DATEPART(Quarter,@dt)*3-Month(@dt), @dt), 120)+'1')--3．指定日期所在月份的第一天或最后一天--A. 月的第一天SELECT CONVERT(datetime,CONVERT(char(8),@dt,120)+'1')--B. 月的最后一天SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+'1')--C. 月的最后一天（容易使用的错误方法）SELECT DATEADD(Month,1,DATEADD(Day,-DAY(@dt),@dt))--4．指定日期所在周的任意一天SELECT DATEADD(Day,@number-DATEPART(Weekday,@dt),@dt)--5．指定日期所在周的任意星期几--A. 星期天做为一周的第1天SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt)--B. 星期一做为一周的第1天SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-2)%7-1,@dt)7、特殊日期加减函数
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 datetimeASBEGIN 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)ENDGO8、查询指定日期段内过生日的人员
--测试数据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 @tWHERE 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.0007 bb 1989-12-11 00:00:00.000--*/--查询 2003-12-05 至 2006-02-28 生日的记录SET @dt2='2006-02-28'SELECT * FROM @tWHERE 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.0002 bb 1996-02-29 00:00:00.0007 bb 1989-12-11 00:00:00.000--*/
9、生成日期列表的函数
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))ASBEGIN 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 RETURNENDGO/*====================================================================*/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))ASBEGIN 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 RETURNENDGO10、工作日处理函数(标准节假日)
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 intASBEGIN 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)ENDGO/*=================================================================*/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 datetimeASBEGIN 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)END11、工作日处理函数(自定义节假日)
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) --假日名称GOif 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 intASBEGIN 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)))ENDGOif 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 datetimeASBEGIN 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)END12、计算工作时间的函数
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) --工作时数(分钟))GOif 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 intASBEGIN 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)END13、复杂年月处理
--定义基本数字表declare @T1 table(代码 int,名称 varchar(10),参加时间 datetime,终止时间 datetime)insert into @T1 select 12,'单位1','2003/04/01','2004/05/01' union all select 22,'单位2','2001/02/01','2003/02/01' union all select 42,'单位3','2000/04/01','2003/05/01' union all select 25,'单位5','2003/04/01','2003/05/01'--定义年表declare @NB table(代码 int,名称 varchar(10),年份 int)insert into @NB select 12,'单位1',2003 union all select 12,'单位1',2004 union all select 22,'单位2',2001 union all select 22,'单位2',2002 union all select 22,'单位2',2003--定义月表declare @YB table(代码 int,名称 varchar(10),年份 int,月份 varchar(2))insert into @YB select 12,'单位1',2003,'04' union all select 22,'单位2',2001,'01' union all select 22,'单位2',2001,'12'--为年表+月表数据处理准备临时表select top 8246 y=identity(int,1753,1)into #tby from (select id from syscolumns) a, (select id from syscolumns) b, (select id from syscolumns) c--为月表数据处理准备临时表select top 12 m=identity(int,1,1)into #tbm from syscolumns/*--数据处理--*/--年表数据处理select a.*from(select a.代码,a.名称,年份=b.yfrom @T1 a,#tby bwhere b.y between year(参加时间) and year(终止时间)) a left join @NB b on a.代码=b.代码 and a.年份=b.年份where b.代码 is null--月表数据处理select a.*from(select a.代码,a.名称,年份=b.y,月份=right('00'+cast(c.m as varchar),2)from @T1 a,#tby b,#tbm cwhere b.y*100+c.m between convert(varchar(6),参加时间,112) and convert(varchar(6),终止时间,112)) a left join @YB b on a.代码=b.代码 and a.年份=b.年份 and a.月份=b.月份where b.代码 is nullorder by a.代码,a.名称,a.年份,a.月份--删除数据处理临时表drop table #tby,#tbm14、 交叉表
--示例--示例数据create table tb(ID int,Time datetime)insert tb select 1,'2005/01/24 16:20'union all select 2,'2005/01/23 22:45'union all select 3,'2005/01/23 0:30'union all select 4,'2005/01/21 4:28'union all select 5,'2005/01/20 13:22'union all select 6,'2005/01/19 20:30'union all select 7,'2005/01/19 18:23'union all select 8,'2005/01/18 9:14'union all select 9,'2005/01/18 18:04'go--查询处理：select case when grouping(b.Time)=1 then 'Total' else b.Time end, [Mon]=sum(case a.week when 1 then 1 else 0 end), [Tue]=sum(case a.week when 2 then 1 else 0 end), [Wed]=sum(case a.week when 3 then 1 else 0 end), [Thu]=sum(case a.week when 4 then 1 else 0 end), [Fri]=sum(case a.week when 5 then 1 else 0 end), [Sat]=sum(case a.week when 6 then 1 else 0 end), [Sun]=sum(case a.week when 0 then 1 else 0 end), [Total]=count(a.week)from( select Time=convert(char(5),dateadd(hour,-1,Time),108) --时间交界点是1am,所以减1小时，避免进行跨天处理 ,week=(@@datefirst+datepart(weekday,Time)-1)%7 --考虑@@datefirst对datepart的影响 from tb)a right join( select id=1,a='16:00',b='19:59',Time='[5pm - 9pm)' union all select id=2,a='20:00',b='23:59',Time='[9pm - 1am)' union all select id=3,a='00:00',b='02:59',Time='[1am - 4am)' union all select id=4,a='03:00',b='07:29',Time='[4am - 8:30am)' union all select id=5,a='07:30',b='11:59',Time='[8:30am - 1pm)' union all select id=6,a='12:00',b='15:59',Time='[1pm - 5pm)')b on a.Time>=b.a and a.Time<b.bgroup by b.id,b.Time with rolluphaving grouping(b.Time)=0 or grouping(b.id)=1go--删除测试drop table tb/*--测试结果 Mon Tue Wed Thu Fri Sat Sun Total -------------- ----- ----- ----- ----- ----- ------ ---- -------[5pm - 9pm) 0 1 2 0 0 0 0 3[9pm - 1am) 0 0 0 0 0 0 2 2[1am - 4am) 0 0 0 0 0 0 0 0[4am - 8:30am) 0 0 0 0 1 0 0 1[8:30am - 1pm) 0 1 0 0 0 0 0 1[1pm - 5pm) 1 0 0 1 0 0 0 2Total 1 2 2 1 1 0 2 9（所影响的行数为 7 行）--*/15、任意两个时间之间的星期几的次数-横
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_weekdaycount]') and xtype in (N'FN', N'IF', N'TF'))drop function [dbo].[f_weekdaycount]GO/*--计算任意两个时间之间的星期几的次数(横向显示) 本方法直接判断 @@datefirst 做对应处理 不受 sp_language 及 set datefirst 的影响 --邹建 2004.08(引用请保留此信息)--*//*--调用示例 select * from f_weekdaycount('2004-9-01','2004-9-02')--*/create function f_weekdaycount(@dt_begin datetime,@dt_end datetime)returns tableasreturn( select 跨周数 ,周一=case a when -1 then case when 1 between b and c then 1 else 0 end when 0 then case when b<=1 then 1 else 0 end +case when c>=1 then 1 else 0 end else a+case when b<=1 then 1 else 0 end +case when c>=1 then 1 else 0 end end ,周二=case a when -1 then case when 2 between b and c then 1 else 0 end when 0 then case when b<=2 then 1 else 0 end +case when c>=2 then 1 else 0 end else a+case when b<=2 then 1 else 0 end +case when c>=2 then 1 else 0 end end ,周三=case a when -1 then case when 3 between b and c then 1 else 0 end when 0 then case when b<=3 then 1 else 0 end +case when c>=3 then 1 else 0 end else a+case when b<=3 then 1 else 0 end +case when c>=3 then 1 else 0 end end ,周四=case a when -1 then case when 4 between b and c then 1 else 0 end when 0 then case when b<=4 then 1 else 0 end +case when c>=4 then 1 else 0 end else a+case when b<=4 then 1 else 0 end +case when c>=4 then 1 else 0 end end ,周五=case a when -1 then case when 5 between b and c then 1 else 0 end when 0 then case when b<=5 then 1 else 0 end +case when c>=5 then 1 else 0 end else a+case when b<=5 then 1 else 0 end +case when c>=5 then 1 else 0 end end ,周六=case a when -1 then case when 6 between b and c then 1 else 0 end when 0 then case when b<=6 then 1 else 0 end +case when c>=6 then 1 else 0 end else a+case when b<=6 then 1 else 0 end +case when c>=6 then 1 else 0 end end ,周日=case a when -1 then case when 0 between b and c then 1 else 0 end when 0 then case when b<=0 then 1 else 0 end +case when c>=0 then 1 else 0 end else a+case when b<=0 then 1 else 0 end +case when c>=0 then 1 else 0 end end from( select 跨周数=case when @dt_begin<@dt_end then (datediff(day,@dt_begin,@dt_end)+7)/7 else (datediff(day,@dt_end,@dt_begin)+7)/7 end ,a=case when @dt_begin<@dt_end then datediff(week,@dt_begin,@dt_end)-1 else datediff(week,@dt_end,@dt_begin)-1 end ,b=case when @dt_begin<@dt_end then (@@datefirst+datepart(weekday,@dt_begin)-1)%7 else (@@datefirst+datepart(weekday,@dt_end)-1)%7 end ,c=case when @dt_begin<@dt_end then (@@datefirst+datepart(weekday,@dt_end)-1)%7 else (@@datefirst+datepart(weekday,@dt_begin)-1)%7 end)a)go16、任意两个时间之间的星期几的次数-纵
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_weekdaycount]') and xtype in (N'FN', N'IF', N'TF'))drop function [dbo].[f_weekdaycount]GO/*--计算任意两个时间之间的星期几的次数(纵向显示) 本方法直接判断 @@datefirst 做对应处理 不受 sp_language 及 set datefirst 的影响 --邹建 2004.08(引用请保留此信息)--*//*--调用示例 select * from f_weekdaycount('2004-8-02','2004-8-8')--*/create function f_weekdaycount(@dt_begin datetime,@dt_end datetime)returns tableasreturn( select 项目='跨周数' ,值=case when @dt_begin<@dt_end then (datediff(day,@dt_begin,@dt_end)+7)/7 else (datediff(day,@dt_end,@dt_begin)+7)/7 end union all select a.a,case b.a when -1 then case when a.b between b.b and b.c then 1 else 0 end when 0 then case when b.b<=a.b then 1 else 0 end +case when b.c>=a.b then 1 else 0 end else b.a+case when b.b<=a.b then 1 else 0 end +case when b.c>=a.b then 1 else 0 end end from(select a='星期一',b=1 union all select '星期二',2 union all select '星期三',3 union all select '星期四',4 union all select '星期五',5 union all select '星期六',6 union all select '星期日',0 )a,(select a=case when @dt_begin<@dt_end then datediff(week,@dt_begin,@dt_end)-1 else datediff(week,@dt_end,@dt_begin)-1 end ,b=case when @dt_begin<@dt_end then (@@datefirst+datepart(weekday,@dt_begin)-1)%7 else (@@datefirst+datepart(weekday,@dt_end)-1)%7 end ,c=case when @dt_begin<@dt_end then (@@datefirst+datepart(weekday,@dt_end)-1)%7 else (@@datefirst+datepart(weekday,@dt_begin)-1)%7 end)b)go17、统计--交叉表+日期+优先
--交叉表,根据优先级取数据,日期处理create table tb(qid int,rid nvarchar(4),tagname nvarchar(10),starttime smalldatetime,endtime smalldatetime,startweekday int,endweekday int,startdate smalldatetime,enddate smalldatetime,d int)insert tb select 1,'A1','未订','08:00','09:00',1 ,5 ,null ,null ,1union all select 1,'A1','未订','09:00','10:00',1 ,5 ,null ,null ,1union all select 1,'A1','未订','10:00','11:00',1 ,5 ,null ,null ,1union all select 1,'A1','装修','08:00','09:00',null,null,'2005-1-18','2005-1-19',2 --union all select 1,'A1','装修','09:00','10:00',null,null,'2005-1-18','2005-1-19',2union all select 1,'A1','装修','10:00','11:00',null,null,'2005-1-18','2005-1-19',2union all select 1,'A2','未订','08:00','09:00',1 ,5 ,null ,null ,1union all select 1,'A2','未订','09:00','10:00',1 ,5 ,null ,null ,1union all select 1,'A2','未订','10:00','11:00',1 ,5 ,null ,null ,1--union all select 1,'A2','装修','08:00','09:00',null,null,'2005-1-18','2005-1-19',2union all select 1,'A2','装修','09:00','10:00',null,null,'2005-1-18','2005-1-19',2--union all select 1,'A2','装修','10:00','11:00',null,null,'2005-1-18','2005-1-19',2go/*--楼主这个问题要考虑几个方面 1. 取星期时,set datefirst 的影响 2. 优先级问题 3. qid,rid 应该是未知的(动态变化的)--*/--实现的存储过程如下create proc p_qry@date smalldatetime --要查询的日期asset nocount ondeclare @week int,@s nvarchar(4000)--格式化日期和得到星期select @date=convert(char(10),@date,120) ,@week=(@@datefirst+datepart(weekday,@date)-1)%7 ,@s=''select id=identity(int),* into #tfrom( select top 100 percent qid,rid,tagname, starttime=convert(char(5),starttime,108), endtime=convert(char(5),endtime,108) from tb where (@week between startweekday and endweekday) or(@date between startdate and enddate) order by qid,rid,starttime,d desc)aselect @s=@s+N',['+rtrim(rid) +N']=max(case when qid='+rtrim(qid) +N' and rid=N'''+rtrim(rid) +N''' then tagname else N'''' end)'from #t group by qid,ridexec('select starttime,endtime'+@s+' from #t awhere not exists( select * from #t where qid=a.qid and rid=a.rid and starttime=a.starttime and endtime=a.endtime and id<a.id)group by starttime,endtime')go--调用exec p_qry '2005-1-17'exec p_qry '2005-1-18'go--删除测试drop table tbdrop proc p_qry/*--测试结果starttime endtime A1 A2 --------- ------- ---------- ---------- 08:00 09:00 未订 未订09:00 10:00 未订 未订10:00 11:00 未订 未订starttime endtime A1 A2 --------- ------- ---------- ---------- 08:00 09:00 装修 未订09:00 10:00 未订 装修10:00 11:00 装修 未订--*/
浙公网安备 33010602011771号