sqlserver里
starttime与endtime字段值分别为
18/03/2006 7:01:01 AM 18/03/2006 7:01:18 AM
18/03/2006 7:01:01 AM 19/03/2006 7:01:01 AM
18/03/2006 7:01:01 AM 19/03/2006 5:01:01 AM
18/03/2006 7:01:01 AM 19/03/2006 9:01:01 AM
我要得到2个字段的差值分别为
00:00:17
1天
22:00:00
1天02:00:00怎么写表达式?
starttime与endtime字段值分别为
18/03/2006 7:01:01 AM 18/03/2006 7:01:18 AM
18/03/2006 7:01:01 AM 19/03/2006 7:01:01 AM
18/03/2006 7:01:01 AM 19/03/2006 5:01:01 AM
18/03/2006 7:01:01 AM 19/03/2006 9:01:01 AM
我要得到2个字段的差值分别为
00:00:17
1天
22:00:00
1天02:00:00怎么写表达式?
returns varchar(1000)
as
begin
return
(
isnull(case when datediff(year,@startdate,@enddate)=0 then null else rtrim(datediff(year,@startdate,@enddate)) end+'年','')+
isnull(case when datediff(month,dateadd(year,datediff(year,@startdate,@enddate),@startdate),@enddate)=0 then null else rtrim(datediff(month,dateadd(year,datediff(year,@startdate,@enddate),@startdate),@enddate)) end+'月','')+
isnull(case when datediff(day,dateadd(month,datediff(month,@startdate,@enddate),@startdate),@enddate)=0 then null else rtrim(datediff(day,dateadd(month,datediff(month,@startdate,@enddate),@startdate),@enddate)) end+'天','')+
(right(100+datediff(hour,dateadd(day,datediff(day,@startdate,@enddate),@startdate),@enddate),2)+':')+
(right(100+datediff(minute,dateadd(hour,datediff(hour,@startdate,@enddate),@startdate),@enddate),2)+':')+
(right(100+datediff(second,dateadd(minute,datediff(minute,@startdate,@enddate),@startdate),@enddate),2))
)
end
go
--for sample
select dbo.F_Datediff('2006-7-28 12:01',getdate())
select dbo.F_Datediff('2005-7-28 12:01',getdate())
select dbo.F_Datediff('2006-1-28 12:01',getdate())
select dbo.F_Datediff('2006-7-25 12:01',getdate())/**/
功能: 返回二时段相隔时间
编写: WangJun
日期: 2006-7-28
说明:后面一个参数必须大于前一个参数测试:
select dbo.uf_getDiffDt ('2005-12-30 06:12:06','2006-1-1 06:12:05')
select dbo.uf_getDiffDt ('2005-12-30 06:12:06','2005-12-30 09:12:05')--结果
1天23:59:5902:59:59更新历史记录:
=========================================*/
create FUNCTION [dbo].[uf_getDiffDt] (
@begDt datetime
, @endDt datetime
) RETURNS varchar(20)AS BEGIN declare @Rtn varchar(20),
@DiffSeconds int,
@Days int,
@Hours int,
@Minutes int,
@Seconds intset @DiffSeconds = datediff(ss,@begDt,@endDt)
set @Days = @DiffSeconds / (24* 60* 60)
set @Hours = @DiffSeconds / ( 60* 60)%24
set @Minutes = @DiffSeconds /60%60
set @Seconds = @DiffSeconds %60
if @Days > 0
begin
set @Rtn = cast(@Days as varchar(5))+'天' + right(cast(100 + @Hours as varchar(5)),2)+ ':'+ right(cast(100 + @Minutes as varchar(5)),2)+ ':' + right(cast(100 + @Seconds as varchar(5)),2)
end else
begin
set @Rtn = right(cast(100 + @Hours as varchar(5)),2)+ ':'+ right(cast(100 + @Minutes as varchar(5)),2)+ ':' + right(cast(100 + @Seconds as varchar(5)),2)
end return @RtnEND
select '00:00:17' 间隔时间,* from 表名 where datediff(minute,starttime,endtime)=17
union all
select '1天' 间隔时间,* from 表名 where datediff(day,starttime,endtime)=1
union all
select '22:00:00' 间隔时间,* from 表名 where datediff(hour,starttime,endtime)=22
union all
select '1天02:00:00' 间隔时间,* from 表名 where datediff(hour,starttime,endtime)=26
select dbo.uf_getDiffDt ('2005-12-30 23:59:06','2006-1-1 00:00:05')
结果就不对了。
select dbo.uf_getDiffDt ('2005-12-30 23:59:06','2006-1-1 00:00:05')
结果就不对了。
<<<<<<怎么不对呀?到'2005-12-31 23:59:06' 是一天,后面还有59秒
结果是:
1天00:00:59
结果是:
1天00:00:59sorry,是我弄错了,非常感谢您!