I am facing some difficulty with calculating the time difference between two dates.
我在计算两个日期之间的时差时遇到了一些困难。
What I want is, I have two dates let say
我想要的是,我有两个约会可以说
@StartDate = '10/01/2012 08:40:18.000'
@EndDate='10/04/2012 09:52:48.000'
so the difference between two dates in the form of hh:mm:ss
is 72:42:30
.
所以这两个日期在hh:mm:ss是72:42:30。
How can I get this result in a T-SQL query?
如何在T-SQL查询中获得这个结果?
13 个解决方案
#1
34
declare @StartDate datetime, @EndDate datetime
select @StartDate = '10/01/2012 08:40:18.000',@EndDate='10/04/2012 09:52:48.000'
select convert(varchar(5),DateDiff(s, @startDate, @EndDate)/3600)+':'+convert(varchar(5),DateDiff(s, @startDate, @EndDate)%3600/60)+':'+convert(varchar(5),(DateDiff(s, @startDate, @EndDate)%60)) as [hh:mm:ss]
This query will helpful to you.
这个查询将对您有所帮助。
#2
19
The shortest code would be:
最短的代码是:
Select CAST((@EndDateTime-@StartDateTime) as time(0)) '[hh:mm:ss]'
#3
9
While maybe not the most efficient, this would work:
虽然这可能不是最有效的方法,但它会起作用:
declare @StartDate datetime, @EndDate datetime
select @StartDate = '10/01/2012 08:40:18.000',@EndDate='10/04/2012 09:52:48.000'
select convert(varchar(5),DateDiff(s, @startDate, @EndDate)/3600)+':'+convert(varchar(5),DateDiff(s, @startDate, @EndDate)%3600/60)+':'+convert(varchar(5),(DateDiff(s, @startDate, @EndDate)%60))
if you can run two selects then this would be better because you only do the datediff once:
如果您可以运行两个select语句,那么这将会更好,因为您只执行一次datediff:
declare @StartDate datetime, @EndDate datetime
select @StartDate = '10/01/2012 08:40:18.000',@EndDate='10/04/2012 09:52:48.000'
declare @Sec BIGINT
select @Sec = DateDiff(s, @startDate, @EndDate)
select convert(varchar(5),@sec/3600)+':'+convert(varchar(5),@sec%3600/60)+':'+convert(varchar(5),(@sec%60))
#4
4
If you're not opposed to implicit type casting I'll offer this an alternative solution. Is it more readable with better formatting? You be the judge.
如果您不反对隐式类型强制转换,我将为您提供另一种解决方案。用更好的格式更容易阅读吗?你是法官。
DECLARE @StartDate datetime = '10/01/2012 08:40:18.000'
,@EndDate datetime = '10/04/2012 09:52:48.000'
SELECT
STR(ss/3600, 5) + ':' + RIGHT('0' + LTRIM(ss%3600/60), 2) + ':' + RIGHT('0' + LTRIM(ss%60), 2) AS [hh:mm:ss]
FROM (VALUES(DATEDIFF(s, @StartDate, @EndDate))) seconds (ss)
#5
2
DECLARE @dt1 datetime='2012/06/13 08:11:12', @dt2 datetime='2012/06/12 02:11:12'
SELECT CAST((@dt2-@dt1) as time(0))
#6
1
I like the idea of making this into a function so it becomes re-useable and your queries become much easier to read:
我喜欢把它变成一个函数,这样它就变成了可重用的,你的查询变得更容易阅读:
--get the difference between two datetimes in the format: 'h:m:s'
CREATE FUNCTION getDateDiff(@startDate DATETIME, @endDate DATETIME)
RETURNS VARCHAR(10)
AS BEGIN
DECLARE @seconds INT = DATEDIFF(s, @startDate, @endDate)
DECLARE @difference VARCHAR(10) =
CONVERT(VARCHAR(4), @seconds / 3600) + ':' +
CONVERT(VARCHAR(2), @seconds % 3600 / 60) + ':' +
CONVERT(VARCHAR(2), @seconds % 60)
RETURN @difference
END
Usage:
用法:
DECLARE @StartDate DATETIME = '10/01/2012 08:40:18.000'
DECLARE @endDate DATETIME = '10/04/2012 09:52:48.000'
SELECT dbo.getDateDiff(@startDate, @endDate) AS DateDifference
Result:
结果:
DateDifference
1 73:12:30
It's also easier to read the result if you add padding so the format is always hh:mm:ss
. For example, here's how you would do that in SQL Server 2012 or later:
如果你添加了填充,也更容易读取结果,所以格式总是hh:mm:ss。例如,在SQL Server 2012或以后的版本中,您可以这样做:
--get the difference between two datetimes in the format: 'hh:mm:ss'
CREATE FUNCTION getDateDiff(@startDate DATETIME, @endDate DATETIME)
RETURNS VARCHAR(10)
AS BEGIN
DECLARE @seconds INT = DATEDIFF(s, @startDate, @endDate)
DECLARE @difference VARCHAR(10) =
FORMAT(@seconds / 3600), '00') + ':' +
FORMAT(@seconds % 3600 / 60, '00') + ':' +
FORMAT(@seconds % 60, '00')
RETURN @difference
END
Note that this will not clip the hour if it is more than 2 digits long. So 1 hour would show up as 01:00:00
and 100 hours would show up as 100:00:00
注意,如果一个小时的长度超过两位数,它将不会被裁剪。1小时显示为01:00:00 100小时显示为100:00:00
#7
0
Take a look at these. I didn't use more parenthesis to keep it readable, so remember that multiplication is done before addition or subtraction.
看看这些。我没有使用更多的括号来保持可读性,所以请记住乘法运算是在加减法之前完成的。
Both below return:
下面两个返回:
hr mins sec timediff
73 12 30 73:12:30
This is written to not use a sub-query and be the most readable and understandable:
这是为了不使用子查询和最可读和可理解:
declare @StartDate datetime,
@EndDate datetime
set @StartDate = '10/01/2012 08:40:18.000'
set @EndDate = '10/04/2012 09:52:48.000'
select datediff(hour, @StartDate, @EndDate) hr,
datediff(minute, @StartDate, @EndDate)
- datediff(hour, @StartDate, @EndDate) * 60 mins,
datediff(second, @StartDate, @EndDate)
- (datediff(minute, @StartDate, @EndDate) * 60) sec,
cast(datediff(hour, @StartDate, @EndDate) as varchar)+':'+
cast(datediff(minute, @StartDate, @EndDate)
- datediff(hour, @StartDate, @EndDate) * 60 as varchar)+':'+
cast(datediff(second, @StartDate, @EndDate)
- (datediff(minute, @StartDate, @EndDate) * 60) as varchar) timediff
This is a version that would perform better if you have a lot of data. It requires a sub-query.
如果您有大量的数据,这个版本的性能会更好。它需要一个子查询。
declare @StartDate datetime,
@EndDate datetime
set @StartDate = '10/01/2012 08:40:18.000'
set @EndDate = '10/04/2012 09:52:48.000'
select s.seconds / 3600 hrs,
s.seconds / 60 - (seconds / 3600 ) * 60 mins,
s.seconds - (s.seconds / 60) * 60 seconds,
cast(s.seconds / 3600 as varchar) + ':' +
cast((s.seconds / 60 - (seconds / 3600 ) * 60) as varchar) + ':' +
cast((s.seconds - (s.seconds / 60) * 60) as varchar) timediff
from (select datediff(second, @StartDate, @EndDate) as seconds) s
#8
0
I came across this post today as I was trying to gather the time difference between fields located in separate tables joined together on a key field. This is the working code for such an endeavor. (tested in sql 2010) Bare in mind that my original query co-joined 6 tables on a common keyfield, in the code below I have removed the other tables as to not cause any confusion for the reader.
我今天偶然看到这篇文章,因为我试图收集位于不同表中的字段之间的时间差异,这些字段在一个关键字段上连接在一起。这是这样的工作代码。(在sql 2010中进行了测试)注意到,我的原始查询在一个通用的关键字段上与6个表联合,在下面的代码中,我删除了其他表,以免引起读者的混淆。
The purpose of the query is to calculate the difference between the variables CreatedUTC & BackupUTC, where difference is expressed in days and the field is called 'DaysActive.'
查询的目的是计算CreatedUTC和BackupUTC变量之间的差异,其中差异以天数表示,字段称为“DaysActive”。
declare @CreatedUTC datetime
declare @BackupUtc datetime
SELECT TOP 500
table02.Column_CreatedUTC AS DeviceCreated,
CAST(DATEDIFF(day, table02.Column_CreatedUTC, table03.Column_EndDateUTC) AS nvarchar(5))+ ' Days' As DaysActive,
table03.Column_EndDateUTC AS LastCompleteBackup
FROM
Operations.table01 AS table01
LEFT OUTER JOIN
dbo.table02 AS table02
ON
table02.Column_KeyField = table01.Column_KeyField
LEFT OUTER JOIN
dbo.table03 AS table03
ON
table01.Column_KeyField = table03.Column_KeyField
Where table03.Column_EndDateUTC > dateadd(hour, -24, getutcdate()) --Gathers records with an end date in the last 24 hours
AND table02.[Column_CreatedUTC] = COALESCE(@CreatedUTC, table02.[Column_CreatedUTC])
AND table03.[Column_EndDateUTC] = COALESCE(@BackupUTC, table03.[Column_EndDateUTC])
GROUP BY table03.Column_EndDateUTC, table02.Column_CreatedUTC
ORDER BY table02.Column_CreatedUTC ASC, DaysActive, table03.Column_EndDateUTC DESC
The Output will be as follows:
输出如下:
[DeviceCreated]..[DaysActive]..[LastCompleteBackup]
---------------------------------------------------------
[2/13/12 16:04]..[463 Days]....[5/21/13 12:14]
[2/12/13 22:37]..[97 Days].....[5/20/13 22:10]
#9
0
It's A Script Write Copy then write in your script file and change your requered field and get out put
它是一个脚本编写的副本,然后在你的脚本文件中写入,并改变你的请求字段,然后退出。
DECLARE @Sdate DATETIME, @Edate DATETIME, @Timediff VARCHAR(100)
SELECT @Sdate = '02/12/2014 08:40:18.000',@Edate='02/13/2014 09:52:48.000'
SET @Timediff=DATEDIFF(s, @Sdate, @Edate)
SELECT CONVERT(VARCHAR(5),@Timediff/3600)+':'+convert(varchar(5),@Timediff%3600/60)+':'+convert(varchar(5),@Timediff%60) AS TimeDiff
#10
0
DECLARE @StartDate datetime = '10/01/2012 08:40:18.000'
,@EndDate datetime = '10/10/2012 09:52:48.000'
,@DaysDifferent int = 0
,@Sec BIGINT
select @Sec = DateDiff(s, @StartDate, @EndDate)
IF (DATEDIFF(day, @StartDate, @EndDate) > 0)
BEGIN
select @DaysDifferent = DATEDIFF(day, @StartDate, @EndDate)
select @Sec = @Sec - ( @DaysDifferent * 86400 )
SELECT LTRIM(STR(@DaysDifferent,3)) +'d '+ LTRIM(STR(@Sec/3600, 5)) + ':' + RIGHT('0' + LTRIM(@Sec%3600/60), 2) + ':' + RIGHT('0' + LTRIM(@Sec%60), 2) AS [dd hh:mm:ss]
END
ELSE
BEGIN
SELECT LTRIM(STR(@DaysDifferent,3)) +'d '+ LTRIM(STR(@Sec/3600, 5)) + ':' + RIGHT('0' + LTRIM(@Sec%3600/60), 2) + ':' + RIGHT('0' + LTRIM(@Sec%60), 2) AS [dd hh:mm:ss]
END
----------------------------------------------------------------------------------
dd HH:MM:SS
9d 1:12:30
#11
0
declare @StartDate datetime, @EndDate datetime
select @StartDate = '2016-05-04 10:23:41.083',@EndDate='2016-05-04 10:25:26.053'
select CAST(DateDiff(MI, @startDate, @EndDate)/60 AS varchar)+':'+Cast(DateDiff(MI, @startDate, @EndDate)%60 AS varchar)+':'+cast(DateDiff(s, @startDate, @EndDate)%60 AS varchar) as [hh:mm:ss]
this will help you too
这对你也有帮助
#12
-1
declare @StartDate datetime, @EndDate datetime
声明@StartDate datetime, @EndDate datetime
set @StartDate = '10/01/2012 08:40:18.000'
set @EndDate = '10/04/2012 09:52:48.000'
SELECT CONVERT(CHAR(8), CAST(CONVERT(varchar(23),@EndDate,121) AS DATETIME)
-CAST(CONVERT(varchar(23),@StartDate,121)AS DATETIME),8) AS TimeDiff
#13
-1
declare @StartDate datetime;
declare @EndDate datetime;
select @StartDate = '10/01/2012 08:40:18.000';
select @EndDate='10/04/2012 09:52:48.000';
select cast(datediff(hour,@StartDate,@EndDate) as varchar(10)) + left(right(cast(cast(cast((@EndDate-@StartDate) as datetime) as time) as varchar(16)),14),6)
#1
34
declare @StartDate datetime, @EndDate datetime
select @StartDate = '10/01/2012 08:40:18.000',@EndDate='10/04/2012 09:52:48.000'
select convert(varchar(5),DateDiff(s, @startDate, @EndDate)/3600)+':'+convert(varchar(5),DateDiff(s, @startDate, @EndDate)%3600/60)+':'+convert(varchar(5),(DateDiff(s, @startDate, @EndDate)%60)) as [hh:mm:ss]
This query will helpful to you.
这个查询将对您有所帮助。
#2
19
The shortest code would be:
最短的代码是:
Select CAST((@EndDateTime-@StartDateTime) as time(0)) '[hh:mm:ss]'
#3
9
While maybe not the most efficient, this would work:
虽然这可能不是最有效的方法,但它会起作用:
declare @StartDate datetime, @EndDate datetime
select @StartDate = '10/01/2012 08:40:18.000',@EndDate='10/04/2012 09:52:48.000'
select convert(varchar(5),DateDiff(s, @startDate, @EndDate)/3600)+':'+convert(varchar(5),DateDiff(s, @startDate, @EndDate)%3600/60)+':'+convert(varchar(5),(DateDiff(s, @startDate, @EndDate)%60))
if you can run two selects then this would be better because you only do the datediff once:
如果您可以运行两个select语句,那么这将会更好,因为您只执行一次datediff:
declare @StartDate datetime, @EndDate datetime
select @StartDate = '10/01/2012 08:40:18.000',@EndDate='10/04/2012 09:52:48.000'
declare @Sec BIGINT
select @Sec = DateDiff(s, @startDate, @EndDate)
select convert(varchar(5),@sec/3600)+':'+convert(varchar(5),@sec%3600/60)+':'+convert(varchar(5),(@sec%60))
#4
4
If you're not opposed to implicit type casting I'll offer this an alternative solution. Is it more readable with better formatting? You be the judge.
如果您不反对隐式类型强制转换,我将为您提供另一种解决方案。用更好的格式更容易阅读吗?你是法官。
DECLARE @StartDate datetime = '10/01/2012 08:40:18.000'
,@EndDate datetime = '10/04/2012 09:52:48.000'
SELECT
STR(ss/3600, 5) + ':' + RIGHT('0' + LTRIM(ss%3600/60), 2) + ':' + RIGHT('0' + LTRIM(ss%60), 2) AS [hh:mm:ss]
FROM (VALUES(DATEDIFF(s, @StartDate, @EndDate))) seconds (ss)
#5
2
DECLARE @dt1 datetime='2012/06/13 08:11:12', @dt2 datetime='2012/06/12 02:11:12'
SELECT CAST((@dt2-@dt1) as time(0))
#6
1
I like the idea of making this into a function so it becomes re-useable and your queries become much easier to read:
我喜欢把它变成一个函数,这样它就变成了可重用的,你的查询变得更容易阅读:
--get the difference between two datetimes in the format: 'h:m:s'
CREATE FUNCTION getDateDiff(@startDate DATETIME, @endDate DATETIME)
RETURNS VARCHAR(10)
AS BEGIN
DECLARE @seconds INT = DATEDIFF(s, @startDate, @endDate)
DECLARE @difference VARCHAR(10) =
CONVERT(VARCHAR(4), @seconds / 3600) + ':' +
CONVERT(VARCHAR(2), @seconds % 3600 / 60) + ':' +
CONVERT(VARCHAR(2), @seconds % 60)
RETURN @difference
END
Usage:
用法:
DECLARE @StartDate DATETIME = '10/01/2012 08:40:18.000'
DECLARE @endDate DATETIME = '10/04/2012 09:52:48.000'
SELECT dbo.getDateDiff(@startDate, @endDate) AS DateDifference
Result:
结果:
DateDifference
1 73:12:30
It's also easier to read the result if you add padding so the format is always hh:mm:ss
. For example, here's how you would do that in SQL Server 2012 or later:
如果你添加了填充,也更容易读取结果,所以格式总是hh:mm:ss。例如,在SQL Server 2012或以后的版本中,您可以这样做:
--get the difference between two datetimes in the format: 'hh:mm:ss'
CREATE FUNCTION getDateDiff(@startDate DATETIME, @endDate DATETIME)
RETURNS VARCHAR(10)
AS BEGIN
DECLARE @seconds INT = DATEDIFF(s, @startDate, @endDate)
DECLARE @difference VARCHAR(10) =
FORMAT(@seconds / 3600), '00') + ':' +
FORMAT(@seconds % 3600 / 60, '00') + ':' +
FORMAT(@seconds % 60, '00')
RETURN @difference
END
Note that this will not clip the hour if it is more than 2 digits long. So 1 hour would show up as 01:00:00
and 100 hours would show up as 100:00:00
注意,如果一个小时的长度超过两位数,它将不会被裁剪。1小时显示为01:00:00 100小时显示为100:00:00
#7
0
Take a look at these. I didn't use more parenthesis to keep it readable, so remember that multiplication is done before addition or subtraction.
看看这些。我没有使用更多的括号来保持可读性,所以请记住乘法运算是在加减法之前完成的。
Both below return:
下面两个返回:
hr mins sec timediff
73 12 30 73:12:30
This is written to not use a sub-query and be the most readable and understandable:
这是为了不使用子查询和最可读和可理解:
declare @StartDate datetime,
@EndDate datetime
set @StartDate = '10/01/2012 08:40:18.000'
set @EndDate = '10/04/2012 09:52:48.000'
select datediff(hour, @StartDate, @EndDate) hr,
datediff(minute, @StartDate, @EndDate)
- datediff(hour, @StartDate, @EndDate) * 60 mins,
datediff(second, @StartDate, @EndDate)
- (datediff(minute, @StartDate, @EndDate) * 60) sec,
cast(datediff(hour, @StartDate, @EndDate) as varchar)+':'+
cast(datediff(minute, @StartDate, @EndDate)
- datediff(hour, @StartDate, @EndDate) * 60 as varchar)+':'+
cast(datediff(second, @StartDate, @EndDate)
- (datediff(minute, @StartDate, @EndDate) * 60) as varchar) timediff
This is a version that would perform better if you have a lot of data. It requires a sub-query.
如果您有大量的数据,这个版本的性能会更好。它需要一个子查询。
declare @StartDate datetime,
@EndDate datetime
set @StartDate = '10/01/2012 08:40:18.000'
set @EndDate = '10/04/2012 09:52:48.000'
select s.seconds / 3600 hrs,
s.seconds / 60 - (seconds / 3600 ) * 60 mins,
s.seconds - (s.seconds / 60) * 60 seconds,
cast(s.seconds / 3600 as varchar) + ':' +
cast((s.seconds / 60 - (seconds / 3600 ) * 60) as varchar) + ':' +
cast((s.seconds - (s.seconds / 60) * 60) as varchar) timediff
from (select datediff(second, @StartDate, @EndDate) as seconds) s
#8
0
I came across this post today as I was trying to gather the time difference between fields located in separate tables joined together on a key field. This is the working code for such an endeavor. (tested in sql 2010) Bare in mind that my original query co-joined 6 tables on a common keyfield, in the code below I have removed the other tables as to not cause any confusion for the reader.
我今天偶然看到这篇文章,因为我试图收集位于不同表中的字段之间的时间差异,这些字段在一个关键字段上连接在一起。这是这样的工作代码。(在sql 2010中进行了测试)注意到,我的原始查询在一个通用的关键字段上与6个表联合,在下面的代码中,我删除了其他表,以免引起读者的混淆。
The purpose of the query is to calculate the difference between the variables CreatedUTC & BackupUTC, where difference is expressed in days and the field is called 'DaysActive.'
查询的目的是计算CreatedUTC和BackupUTC变量之间的差异,其中差异以天数表示,字段称为“DaysActive”。
declare @CreatedUTC datetime
declare @BackupUtc datetime
SELECT TOP 500
table02.Column_CreatedUTC AS DeviceCreated,
CAST(DATEDIFF(day, table02.Column_CreatedUTC, table03.Column_EndDateUTC) AS nvarchar(5))+ ' Days' As DaysActive,
table03.Column_EndDateUTC AS LastCompleteBackup
FROM
Operations.table01 AS table01
LEFT OUTER JOIN
dbo.table02 AS table02
ON
table02.Column_KeyField = table01.Column_KeyField
LEFT OUTER JOIN
dbo.table03 AS table03
ON
table01.Column_KeyField = table03.Column_KeyField
Where table03.Column_EndDateUTC > dateadd(hour, -24, getutcdate()) --Gathers records with an end date in the last 24 hours
AND table02.[Column_CreatedUTC] = COALESCE(@CreatedUTC, table02.[Column_CreatedUTC])
AND table03.[Column_EndDateUTC] = COALESCE(@BackupUTC, table03.[Column_EndDateUTC])
GROUP BY table03.Column_EndDateUTC, table02.Column_CreatedUTC
ORDER BY table02.Column_CreatedUTC ASC, DaysActive, table03.Column_EndDateUTC DESC
The Output will be as follows:
输出如下:
[DeviceCreated]..[DaysActive]..[LastCompleteBackup]
---------------------------------------------------------
[2/13/12 16:04]..[463 Days]....[5/21/13 12:14]
[2/12/13 22:37]..[97 Days].....[5/20/13 22:10]
#9
0
It's A Script Write Copy then write in your script file and change your requered field and get out put
它是一个脚本编写的副本,然后在你的脚本文件中写入,并改变你的请求字段,然后退出。
DECLARE @Sdate DATETIME, @Edate DATETIME, @Timediff VARCHAR(100)
SELECT @Sdate = '02/12/2014 08:40:18.000',@Edate='02/13/2014 09:52:48.000'
SET @Timediff=DATEDIFF(s, @Sdate, @Edate)
SELECT CONVERT(VARCHAR(5),@Timediff/3600)+':'+convert(varchar(5),@Timediff%3600/60)+':'+convert(varchar(5),@Timediff%60) AS TimeDiff
#10
0
DECLARE @StartDate datetime = '10/01/2012 08:40:18.000'
,@EndDate datetime = '10/10/2012 09:52:48.000'
,@DaysDifferent int = 0
,@Sec BIGINT
select @Sec = DateDiff(s, @StartDate, @EndDate)
IF (DATEDIFF(day, @StartDate, @EndDate) > 0)
BEGIN
select @DaysDifferent = DATEDIFF(day, @StartDate, @EndDate)
select @Sec = @Sec - ( @DaysDifferent * 86400 )
SELECT LTRIM(STR(@DaysDifferent,3)) +'d '+ LTRIM(STR(@Sec/3600, 5)) + ':' + RIGHT('0' + LTRIM(@Sec%3600/60), 2) + ':' + RIGHT('0' + LTRIM(@Sec%60), 2) AS [dd hh:mm:ss]
END
ELSE
BEGIN
SELECT LTRIM(STR(@DaysDifferent,3)) +'d '+ LTRIM(STR(@Sec/3600, 5)) + ':' + RIGHT('0' + LTRIM(@Sec%3600/60), 2) + ':' + RIGHT('0' + LTRIM(@Sec%60), 2) AS [dd hh:mm:ss]
END
----------------------------------------------------------------------------------
dd HH:MM:SS
9d 1:12:30
#11
0
declare @StartDate datetime, @EndDate datetime
select @StartDate = '2016-05-04 10:23:41.083',@EndDate='2016-05-04 10:25:26.053'
select CAST(DateDiff(MI, @startDate, @EndDate)/60 AS varchar)+':'+Cast(DateDiff(MI, @startDate, @EndDate)%60 AS varchar)+':'+cast(DateDiff(s, @startDate, @EndDate)%60 AS varchar) as [hh:mm:ss]
this will help you too
这对你也有帮助
#12
-1
declare @StartDate datetime, @EndDate datetime
声明@StartDate datetime, @EndDate datetime
set @StartDate = '10/01/2012 08:40:18.000'
set @EndDate = '10/04/2012 09:52:48.000'
SELECT CONVERT(CHAR(8), CAST(CONVERT(varchar(23),@EndDate,121) AS DATETIME)
-CAST(CONVERT(varchar(23),@StartDate,121)AS DATETIME),8) AS TimeDiff
#13
-1
declare @StartDate datetime;
declare @EndDate datetime;
select @StartDate = '10/01/2012 08:40:18.000';
select @EndDate='10/04/2012 09:52:48.000';
select cast(datediff(hour,@StartDate,@EndDate) as varchar(10)) + left(right(cast(cast(cast((@EndDate-@StartDate) as datetime) as time) as varchar(16)),14),6)