贴个老大的,你参考参考 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
declare @a table([datetime] datetime) insert @a select '2006-03-02 11:00:00' union all select '2006-03-02 12:00:00'select [time]=case when [datetime] between '2006-03-02 11:00:00' and '2006-03-02 12:00:00' then '01:00:00'else '' end from @a a 结果:(所影响的行数为 2 行)time -------- 01:00:00 01:00:00(所影响的行数为 2 行)
right(datediff(minute,'2006-03-02 11:00:00','2006-03-02 12:00:00')%60+100,2)+':'+
right(datediff(second,'2006-03-02 11:00:00','2006-03-02 12:00:00')%60+100,2)
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
select right(datediff(hour,'2006-03-02 11:00:00','2006-03-02 12:00:00')%24+100,2)+':'+
right(datediff(minute,'2006-03-02 11:00:00','2006-03-02 12:00:00')%60+100,2)+':'+
right(datediff(second,'2006-03-02 11:00:00','2006-03-02 12:00:00')%60+100,2)
是錯誤的,出現跨小時的時間相減時會多將不滿一個小時算為一個小時.比如:
select datediff(HH,'2006-09-07 16:43:52.417','2006-09-07 17:03:02.330')結果為1小時,而實際結果為20分鐘.這個問題令我也頭疼.
这样可以吗
select right(datediff(minute,'2006-09-07 16:43:52.417','2006-09-07 17:03:02.330')/60 %24+100,2)+':'+
right(datediff(second,'2006-09-07 16:43:52.417','2006-09-07 17:03:02.330')/60 %60+100,2)+':'+
right(datediff(millisecond,'2006-09-07 16:43:52.417','2006-09-07 17:03:02.330')/1000 %60+100,2)
先计算两个时间想差的秒数,这个用DATEDIFF可以直接得到,在把秒数转化成HH:MM:SS格式
insert @a
select '2006-03-02 11:00:00' union all
select '2006-03-02 12:00:00'select [time]=case when [datetime] between '2006-03-02 11:00:00' and '2006-03-02 12:00:00' then '01:00:00'else '' end
from @a a
结果:(所影响的行数为 2 行)time
--------
01:00:00
01:00:00(所影响的行数为 2 行)