blocks|key|3067184|text|关于DateDiff，需要注意的是，它计算了您通过指定为单位的边界的次数，因此如果您正在寻找精确的时间跨度，则会出现问题。例如：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3067185|select+datediff+(m,+'20100131',+'20100201')|code-block|syntax|javascript|3067186|给出的答案是1，因为它从1月到2月跨越了边界，所以即使跨度是2天，datediff也会返回值1-它跨越了1个日期边界。|3067187|select+datediff(mi,+'2010-01-22+15:29:55.090'+,+'2010-01-22+15:30:09.153')|3067188|给出值1，同样，它通过了分钟边界一次，所以即使它大约是14秒，当使用分钟作为单位时，它将作为单分钟返回。|3067189|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Just a caveat to add about DateDiff, it counts the number of times you pass the boundary you specify as your units, so is subject to problems if you are looking for a precise timespan.
e.g.

<pre><code>select datediff (m, '20100131', '20100201')
</code></pre>

gives an answer of 1, because it crossed the boundary from January to February, so even though the span is 2 days, datediff would return a value of 1 - it crossed 1 date boundary.

<pre><code>select datediff(mi, '2010-01-22 15:29:55.090' , '2010-01-22 15:30:09.153')
</code></pre>

Gives a value of 1, again, it passed the minute boundary once, so even though it is approx 14 seconds, it would be returned as a single minute when using Minutes as the units.

blocks|key|3067137|text|SELECT+DATEDIFF+(MyUnits,+'2010-01-22+15:29:55.090',+'2010-01-22+15:30:09.153')|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3067138|基于DATEDIFF+on+MSDN替换"MyUnits“|unstyled|offset|length|3067139|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms189794.aspx^0|0|2|G|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|R|8|@]|9|@$G|S|H|T|1|U]]|A|$]]|$1|I|3|-4|5|F|7|V|8|@]|9|@]|A|$]]]|J|$K|$5|L|M|N|A|$O|P]]]]

<pre><code>SELECT DATEDIFF (MyUnits, '2010-01-22 15:29:55.090', '2010-01-22 15:30:09.153')
</code></pre>

Substitute "MyUnits" based on <a href="http://msdn.microsoft.com/en-us/library/ms189794.aspx" rel="noreferrer">DATEDIFF on MSDN</a>

blocks|key|317118|text|SELECT++DATEDIFF(day,+'2010-01-22+15:29:55.090',+'2010-01-22+15:30:09.153')|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|317119|将day替换为您想要获得差异的其他单位，如second、minute等。|unstyled|offset|length|style|CODE|317120|entityMap^0|0|1|3|L|6|S|6|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|N|8|@$G|O|H|P|I|J]|$G|Q|H|R|I|J]|$G|S|H|T|I|J]]|9|@]|A|$]]|$1|K|3|-4|5|F|7|U|8|@]|9|@]|A|$]]]|L|$]]

<pre><code>SELECT DATEDIFF(day, '2010-01-22 15:29:55.090', '2010-01-22 15:30:09.153')
</code></pre>

Replace <code>day</code> with other units you want to get the difference in, like <code>second</code>, <code>minute</code> etc.

blocks|key|317253|text|我可以提一下MS+SQL+Server的四个非常有用的重要功能：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|317254|1)+DATEDIFF()函数负责计算两个日期的差值，结果可以是第一个参数(datepart)中指定的“datepart+year+year+first+on+day+day+hour+first+second毫秒微秒纳秒”：|offset|length|style|BOLD|317255|select+datediff(day,'1997-10-07','2011-09-11')|code-block|syntax|javascript|317256|2)您可以使用函数GETDATE()获取实际时间，并计算某个日期与实际日期的差值：|317257|select+datediff(day,'1997-10-07',+getdate()+)|317258|3)另一个重要的函数是DATEADD()，用于使用的datepart转换datetime中的一些值，您可以将其添加(具有正值)或减去(具有负值)到一个基准日期：|317259|select+DATEADD(day,++45,+getdate())+--+actual+datetime+adding+45+days
select+DATEADD(++s,-638,+getdate())+--+actual+datetime+subtracting+10+minutes+and+38+seconds|317260|4)+CONVERT()函数是用来格式化您需要的日期的，它不是参数函数，但是您可以使用结果的一部分来格式化您需要的结果：|317261|select+convert(++char(8),+getdate()+,+++8)+--+part+hh:mm:ss+of+actual+datetime
select+convert(++varchar,+getdate()+,+112)+--+yyyymmdd
select+convert(+char(10),+getdate()+,++20)+--+yyyy-mm-dd+limited+by+10+characters|317262|DATETIME可以以秒为单位计算，混合这四个函数的一个有趣的结果是显示两个日期之间的格式差异(hh:mm:ss)：|317263|declare++@date1+datetime,+@date2+datetime
set+@date1=DATEADD(s,-638,getdate())
set+@date2=GETDATE()

select+convert(char(8),dateadd(s,datediff(s,@date1,@date2),'1900-1-1'),8)|317264|..。结果是00:+10+:+38++(638秒=600秒%2B38秒=10分38秒)|317265|另一个例子：|317266|select+distinct+convert(char(8),dateadd(s,datediff(s,+CRDATE+,+GETDATE()+),'1900-1-1'),8)+from+sysobjects+order+by+1|317267|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/pt-br/library/ms189794.aspx|1|http://msdn.microsoft.com/pt-br/library/ms188383.aspx|2|http://msdn.microsoft.com/pt-br/library/ms186819.aspx|3|http://msdn.microsoft.com/pt-br/library/ms187928.aspx^0|0|12|8|3|A|0|0|0|9|9|1|0|0|P|9|B|9|2|0|0|3|9|3|0|0|1C|8|0|0|6|C|0|0|0^^$0|@$1|2|3|4|5|6|7|1M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1N|8|@$D|1O|E|1P|F|G]]|9|@$D|1Q|E|1R|1|1S]]|A|$]]|$1|H|3|I|5|J|7|1T|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|1U|8|@]|9|@$D|1V|E|1W|1|1X]]|A|$]]|$1|O|3|P|5|J|7|1Y|8|@]|9|@]|A|$K|L]]|$1|Q|3|R|5|6|7|1Z|8|@$D|20|E|21|F|G]]|9|@$D|22|E|23|1|24]]|A|$]]|$1|S|3|T|5|J|7|25|8|@]|9|@]|A|$K|L]]|$1|U|3|V|5|6|7|26|8|@]|9|@$D|27|E|28|1|29]]|A|$]]|$1|W|3|X|5|J|7|2A|8|@]|9|@]|A|$K|L]]|$1|Y|3|Z|5|6|7|2B|8|@$D|2C|E|2D|F|G]]|9|@]|A|$]]|$1|10|3|11|5|J|7|2E|8|@]|9|@]|A|$K|L]]|$1|12|3|13|5|6|7|2F|8|@$D|2G|E|2H|F|G]]|9|@]|A|$]]|$1|14|3|15|5|6|7|2I|8|@]|9|@]|A|$]]|$1|16|3|17|5|J|7|2J|8|@]|9|@]|A|$K|L]]|$1|18|3|-4|5|6|7|2K|8|@]|9|@]|A|$]]]|19|$1A|$5|1B|1C|1D|A|$1E|1F]]|1G|$5|1B|1C|1D|A|$1E|1H]]|1I|$5|1B|1C|1D|A|$1E|1J]]|1K|$5|1B|1C|1D|A|$1E|1L]]]]

I can mention four important functions of MS SQL Server that can be very useful:

1) The function <a href="http://msdn.microsoft.com/pt-br/library/ms189794.aspx" rel="noreferrer">DATEDIFF()</a> is responsible to calculate differences between two dates, the result could be "year quarter month dayofyear day week hour minute second millisecond microsecond nanosecond", specified on the first parameter (datepart):

<pre><code>select datediff(day,'1997-10-07','2011-09-11')
</code></pre>

2) You can use the function <a href="http://msdn.microsoft.com/pt-br/library/ms188383.aspx" rel="noreferrer">GETDATE()</a> to get the actual time and calculate differences of some date and actual date:

<pre><code>select datediff(day,'1997-10-07', getdate() )
</code></pre>

3) Another important function is <a href="http://msdn.microsoft.com/pt-br/library/ms186819.aspx" rel="noreferrer">DATEADD()</a>, used to convert some value in datetime using the same datepart of the datediff, that you can add (with positive values) or substract (with negative values) to one base date:

<pre><code>select DATEADD(day, 45, getdate()) -- actual datetime adding 45 days
select DATEADD( s,-638, getdate()) -- actual datetime subtracting 10 minutes and 38 seconds
</code></pre>

4) The function <a href="http://msdn.microsoft.com/pt-br/library/ms187928.aspx" rel="noreferrer">CONVERT()</a> was made to format the date like you need, it is not parametric function, but you can use part of the result to format the result like you need:

<pre><code>select convert( char(8), getdate() , 8) -- part hh:mm:ss of actual datetime
select convert( varchar, getdate() , 112) -- yyyymmdd
select convert( char(10), getdate() , 20) -- yyyy-mm-dd limited by 10 characters
</code></pre>

DATETIME cold be calculated in seconds and one interesting result mixing these four function is to show a formated difference um hours, minutes and seconds (hh:mm:ss) between two dates:

<pre><code>declare @date1 datetime, @date2 datetime
set @date1=DATEADD(s,-638,getdate())
set @date2=GETDATE()

select convert(char(8),dateadd(s,datediff(s,@date1,@date2),'1900-1-1'),8)
</code></pre>

... the result is 00:10:38 (638s = 600s + 38s = 10 minutes and 38 seconds)

Another example:

<pre><code>select distinct convert(char(8),dateadd(s,datediff(s, CRDATE , GETDATE() ),'1900-1-1'),8) from sysobjects order by 1
</code></pre>

blocks|key|317358|text|我试过这种方法，它起作用了。我使用的是SQL+Server+2016版|type|unstyled|depth|inlineStyleRanges|entityRanges|data|317359|SELECT+DATEDIFF(MILLISECOND,'2010-01-22+15:29:55.090',+'2010-01-22+15:30:09.153')/1000.00;|code-block|syntax|javascript|317360|不同的DATEDIFF函数包括：|317361|SELECT+DATEDIFF(year,++++++++'2005-12-31+23:59:59.9999999',+'2006-01-01+00:00:00.0000000');
SELECT+DATEDIFF(quarter,+++++'2005-12-31+23:59:59.9999999',+'2006-01-01+00:00:00.0000000');
SELECT+DATEDIFF(month,+++++++'2005-12-31+23:59:59.9999999',+'2006-01-01+00:00:00.0000000');
SELECT+DATEDIFF(dayofyear,+++'2005-12-31+23:59:59.9999999',+'2006-01-01+00:00:00.0000000');
SELECT+DATEDIFF(day,+++++++++'2005-12-31+23:59:59.9999999',+'2006-01-01+00:00:00.0000000');
SELECT+DATEDIFF(week,++++++++'2005-12-31+23:59:59.9999999',+'2006-01-01+00:00:00.0000000');
SELECT+DATEDIFF(hour,++++++++'2005-12-31+23:59:59.9999999',+'2006-01-01+00:00:00.0000000');
SELECT+DATEDIFF(minute,++++++'2005-12-31+23:59:59.9999999',+'2006-01-01+00:00:00.0000000');
SELECT+DATEDIFF(second,++++++'2005-12-31+23:59:59.9999999',+'2006-01-01+00:00:00.0000000');
SELECT+DATEDIFF(millisecond,+'2005-12-31+23:59:59.9999999',+'2006-01-01+00:00:00.0000000');|317362|参考：https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017|offset|length|317363|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017^0|0|0|0|0|3|2N|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|10|8|@]|9|@$M|11|N|12|1|13]]|A|$]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

I tried this way and it worked. I used SQL Server version 2016

<pre><code>SELECT DATEDIFF(MILLISECOND,'2010-01-22 15:29:55.090', '2010-01-22 15:30:09.153')/1000.00;
</code></pre>

Different DATEDIFF Functions are:

<pre><code>SELECT DATEDIFF(year, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(quarter, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(month, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(dayofyear, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(day, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(week, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(hour, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(minute, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(second, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
</code></pre>

Ref: <a href="https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017" rel="noreferrer">https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017</a>

blocks|key|3280574|text|好的，我们都知道答案涉及DATEDIFF()。但这只给了你可能想要的一半的结果。如果您希望以人类可读的格式获得结果，即两个DATETIME值之间的分钟和秒，该怎么办？|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3280575|当然，CONVERT()、DATEADD()和DATEDIFF()函数非常适合于获得更易读的结果，您的客户端可以使用它们而不是数字。|3280576|即|3280577|CONVERT(varchar(5),+DATEADD(minute,+DATEDIFF(MINUTE,+date1,+date2),+0),+114)+|code-block|syntax|javascript|3280578|这将为您提供类似以下内容：|3280579|3280580|+HH:MM|blockquote|3280581|3280582|3280583|如果想要更高的精度，只需增加VARCHAR()即可。|3280584|CONVERT(varchar(12),+DATEADD(minute,+DATEDIFF(MINUTE,+date1,+date2),+0),+114)+|3280585|3280586|+HH:MM.SS.MS|3280587|3280588|entityMap^0|C|A|1P|8|0|3|9|D|9|N|A|0|0|0|0|0|0|0|0|E|9|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|16|8|@$9|17|A|18|B|C]|$9|19|A|1A|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|1B|8|@$9|1C|A|1D|B|C]|$9|1E|A|1F|B|C]|$9|1G|A|1H|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|1I|8|@]|D|@]|E|$]]|$1|J|3|K|5|L|7|1J|8|@]|D|@]|E|$M|N]]|$1|O|3|P|5|6|7|1K|8|@]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|1L|8|@]|D|@]|E|$]]|$1|R|3|S|5|T|7|1M|8|@]|D|@]|E|$]]|$1|U|3|-4|5|6|7|1N|8|@]|D|@]|E|$]]|$1|V|3|-4|5|6|7|1O|8|@]|D|@]|E|$]]|$1|W|3|X|5|6|7|1P|8|@$9|1Q|A|1R|B|C]]|D|@]|E|$]]|$1|Y|3|Z|5|L|7|1S|8|@]|D|@]|E|$M|N]]|$1|10|3|-4|5|6|7|1T|8|@]|D|@]|E|$]]|$1|11|3|12|5|T|7|1U|8|@]|D|@]|E|$]]|$1|13|3|-4|5|6|7|1V|8|@]|D|@]|E|$]]|$1|14|3|-4|5|6|7|1W|8|@]|D|@]|E|$]]]|15|$]]

Ok we all know the answer involves <code>DATEDIFF()</code>. But that gives you only half the result you may be after. What if you want to get the results in human-readable format, in terms of Minutes and Seconds between two <code>DATETIME</code> values?

The <code>CONVERT()</code>, <code>DATEADD()</code> and of course <code>DATEDIFF()</code> functions are perfect for a more easily readable result that your clients can use, instead of a number.

i.e.

<pre><code>CONVERT(varchar(5), DATEADD(minute, DATEDIFF(MINUTE, date1, date2), 0), 114) 
</code></pre>

This will give you something like:

<blockquote>
 HH:MM
</blockquote>

If you want more precision, just increase the <code>VARCHAR()</code>.

<pre><code>CONVERT(varchar(12), DATEADD(minute, DATEDIFF(MINUTE, date1, date2), 0), 114) 
</code></pre>

<blockquote>
 HH:MM.SS.MS
</blockquote>

blocks|key|3067208|text|在SQL+Server内部，日期存储为2个整数。第一个整数是基准日期(1900/01/01)之前或之后的日期数。第二个整数存储午夜后的时钟滴答数，每个滴答数是1/300秒。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3067209|More+info+here|offset|length|3067210|正因为如此，我经常发现比较日期的最简单方法是简单地减去它们。这可以处理我90%25的用例。例如，|3067211|select+date1,+date2,+date2+-+date1+as+DifferenceInDays
from+MyTable
...|code-block|syntax|javascript|3067212|当我需要一个单位而不是天的答案时，我会使用DateDiff。|3067213|entityMap|0|LINK|mutability|MUTABLE|url|http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/how-are-dates-stored-in-sql-server|1|http://msdn.microsoft.com/en-us/library/ms189794.aspx^0|0|0|E|0|0|0|0|L|8|1|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Z|8|@]|9|@$D|10|E|11|1|12]]|A|$]]|$1|F|3|G|5|6|7|13|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|14|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|15|8|@]|9|@$D|16|E|17|1|18]]|A|$]]|$1|O|3|-4|5|6|7|19|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]|W|$5|R|S|T|A|$U|X]]]]

Internally in SQL Server dates are stored as 2 integers. The first integer is the number of dates before or after the base date (1900/01/01). The second integer stores the number of clock ticks after midnight, each tick is 1/300 of a second. 

<a href="http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/how-are-dates-stored-in-sql-server" rel="noreferrer">More info here</a>

Because of this, I often find the simplest way to compare dates is to simply substract them. This handles 90% of my use cases. E.g., 

<pre><code>select date1, date2, date2 - date1 as DifferenceInDays
from MyTable
...
</code></pre>

When I need an answer in units other than days, I will use <a href="http://msdn.microsoft.com/en-us/library/ms189794.aspx" rel="noreferrer">DateDiff</a>.

blocks|key|3280335|text|有许多方法可以查看日期差异，当比较日期/时间时，还有更多的方法。下面是我用来计算格式为“HH:MM:SS”的两个日期之间的差异：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3280336|ElapsedTime+AS
++++++RIGHT('0'+%2B+CAST(DATEDIFF(S,+StartDate,+EndDate)++++++++/+3600+AS+VARCHAR(2)),+2)+%2B+':'
++++%2B+RIGHT('0'+%2B+CAST(DATEDIFF(S,+StartDate,+EndDate)+%25+3600+/+++60+AS+VARCHAR(2)),+2)+%2B+':'
++++%2B+RIGHT('0'+%2B+CAST(DATEDIFF(S,+StartDate,+EndDate)+%25+++60++++++++AS+VARCHAR(2)),+2)|code-block|syntax|javascript|3280337|我将其用于计算列，但您可以简单地将其重写为UDF或查询计算。请注意，此逻辑向下舍入小数秒；00:00.00到00:00.999被认为是零秒，并显示为"00:00:00“。|3280338|如果预期周期可能超过几天，则此代码将在需要时切换为D:HH:MM:SS格式：|3280339|ElapsedTime+AS
++++CASE+WHEN+DATEDIFF(S,+StartDate,+EndDate)+>=+359999
++++++++THEN
++++++++++++++++++++++++++CAST(DATEDIFF(S,+StartDate,+EndDate)+/+86400++++++++AS+VARCHAR(7))+%2B+':'
++++++++++++%2B+RIGHT('0'+%2B+CAST(DATEDIFF(S,+StartDate,+EndDate)+%25+86400+/+3600+AS+VARCHAR(2)),+2)+%2B+':'
++++++++++++%2B+RIGHT('0'+%2B+CAST(DATEDIFF(S,+StartDate,+EndDate)+%25++3600+/+++60+AS+VARCHAR(2)),+2)+%2B+':'
++++++++++++%2B+RIGHT('0'+%2B+CAST(DATEDIFF(S,+StartDate,+EndDate)+%25++++60++++++++AS+VARCHAR(2)),+2)
++++++++ELSE
++++++++++++++RIGHT('0'+%2B+CAST(DATEDIFF(S,+StartDate,+EndDate)++++++++/+3600+AS+VARCHAR(2)),+2)+%2B+':'
++++++++++++%2B+RIGHT('0'+%2B+CAST(DATEDIFF(S,+StartDate,+EndDate)+%25+3600+/+++60+AS+VARCHAR(2)),+2)+%2B+':'
++++++++++++%2B+RIGHT('0'+%2B+CAST(DATEDIFF(S,+StartDate,+EndDate)+%25+++60++++++++AS+VARCHAR(2)),+2)
++++++++END|3280340|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|S|8|@]|9|@]|A|$E|F]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

There are a number of ways to look at a date difference, and more when comparing date/times. Here's what I use to get the difference between two dates formatted as "HH:MM:SS":

<pre><code>ElapsedTime AS
 RIGHT('0' + CAST(DATEDIFF(S, StartDate, EndDate) / 3600 AS VARCHAR(2)), 2) + ':'
 + RIGHT('0' + CAST(DATEDIFF(S, StartDate, EndDate) % 3600 / 60 AS VARCHAR(2)), 2) + ':'
 + RIGHT('0' + CAST(DATEDIFF(S, StartDate, EndDate) % 60 AS VARCHAR(2)), 2)
</code></pre>

I used this for a calculated column, but you could trivially rewrite it as a UDF or query calculation. Note that this logic rounds down fractional seconds; 00:00.00 to 00:00.999 is considered zero seconds, and displayed as "00:00:00".

If you anticipate that periods may be more than a few days long, this code switches to D:HH:MM:SS format when needed:

<pre><code>ElapsedTime AS
 CASE WHEN DATEDIFF(S, StartDate, EndDate) &gt;= 359999
 THEN
 CAST(DATEDIFF(S, StartDate, EndDate) / 86400 AS VARCHAR(7)) + ':'
 + RIGHT('0' + CAST(DATEDIFF(S, StartDate, EndDate) % 86400 / 3600 AS VARCHAR(2)), 2) + ':'
 + RIGHT('0' + CAST(DATEDIFF(S, StartDate, EndDate) % 3600 / 60 AS VARCHAR(2)), 2) + ':'
 + RIGHT('0' + CAST(DATEDIFF(S, StartDate, EndDate) % 60 AS VARCHAR(2)), 2)
 ELSE
 RIGHT('0' + CAST(DATEDIFF(S, StartDate, EndDate) / 3600 AS VARCHAR(2)), 2) + ':'
 + RIGHT('0' + CAST(DATEDIFF(S, StartDate, EndDate) % 3600 / 60 AS VARCHAR(2)), 2) + ':'
 + RIGHT('0' + CAST(DATEDIFF(S, StartDate, EndDate) % 60 AS VARCHAR(2)), 2)
 END
</code></pre>

blocks|key|317282|text|下面的查询应该给出您正在寻找的确切内容。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|317283|select+datediff(second,+'2010-01-22+15:29:55.090'+,+'2010-01-22+15:30:09.153')|code-block|syntax|javascript|317284|这里是来自MSDN的链接，您可以使用datediff函数执行的所有操作。https://msdn.microsoft.com/en-us/library/ms189794.aspx|offset|length|317285|entityMap|0|LINK|mutability|MUTABLE|url|https://msdn.microsoft.com/en-us/library/ms189794.aspx^0|0|0|10|1I|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

The following query should give the exact stuff you are looking out for.

<pre><code>select datediff(second, '2010-01-22 15:29:55.090' , '2010-01-22 15:30:09.153')
</code></pre>

Here is the link from MSDN for what all you can do with datediff function .
<a href="https://msdn.microsoft.com/en-us/library/ms189794.aspx" rel="noreferrer">https://msdn.microsoft.com/en-us/library/ms189794.aspx</a>

blocks|key|319181|text|SELECT+DATEDIFF(yyyy,+'2011/08/25',+'2017/08/25')+AS+DateDiff|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|319182|它可以让你在Year中的两个日期之间产生差异|unstyled|offset|length|style|BOLD|319183|这里(2017-2011)=6个结果|319184|语法：|319185|DATEDIFF(interval,+date1,+date2)|319186|entityMap^0|0|6|4|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|T|8|@$G|U|H|V|I|J]]|9|@]|A|$]]|$1|K|3|L|5|F|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|F|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$B|C]]|$1|Q|3|-4|5|F|7|Z|8|@]|9|@]|A|$]]]|R|$]]

<pre><code>SELECT DATEDIFF(yyyy, '2011/08/25', '2017/08/25') AS DateDiff
</code></pre>

It's gives you difference between two dates in Year 

Here (2017-2011)=6 as a result

Syntax:

<pre><code>DATEDIFF(interval, date1, date2)
</code></pre>

blocks|key|3067477|text|将其用于DD:MM:SS|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3067478|SELECT+CONVERT(VARCHAR(max),+Datediff(dd,+'2019-08-14+03:16:51.360',+
+++++++++'2019-08-15+05:45:37.610'))+
+++++++%2B+':'+
+++++++%2B+CONVERT(CHAR(8),+Dateadd(s,+Datediff(s,+'2019-08-14+03:16:51.360',+
+++++++++'2019-08-15+05:45:37.610'),+'1900-1-1'),+8)+|code-block|syntax|javascript|3067479|entityMap^0|4|8|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

Use This for <code>DD:MM:SS</code>:

<pre class="lang-sql prettyprint-override"><code>SELECT CONVERT(VARCHAR(max), Datediff(dd, '2019-08-14 03:16:51.360', 
 '2019-08-15 05:45:37.610')) 
 + ':' 
 + CONVERT(CHAR(8), Dateadd(s, Datediff(s, '2019-08-14 03:16:51.360', 
 '2019-08-15 05:45:37.610'), '1900-1-1'), 8) 
</code></pre>

blocks|key|3067224|text|所以这不是我的答案，但我只是在网上搜索类似这样的问题时发现了这个。这家伙设置了一个程序来计算小时、分钟和秒。link和代码：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3067225|--Creating+Function
If+OBJECT_ID('UFN_HourMinuteSecond')+Is+Not+Null
Drop+Function+dbo.UFN_HourMinuteSecond
Go
Exec(
'Create+Function+dbo.UFN_HourMinuteSecond
(
@StartDateTime+DateTime,
@EndDateTime+DateTime
)+Returns+Varchar(10)+
As
Begin

Declare+@Seconds+Int,
@Minute+Int,
@Hour+Int,
@Elapsed+Varchar(10)

Select+@Seconds+=+ABS(DateDiff(SECOND+,@StartDateTime,@EndDateTime))

If+@Seconds+>=+60+
Begin
select+@Minute+=+@Seconds/60
select+@Seconds+=+@Seconds%2560

If+@Minute+>=+60
begin
select+@hour+=+@Minute/60
select+@Minute+=+@Minute%2560
end

Else
Goto+Final+
End

Final:
Select+@Hour+=+Isnull(@Hour,0),+@Minute+=+IsNull(@Minute,0),+@Seconds+=+++++++++++++++IsNull(@Seconds,0)
select+@Elapsed+=+Cast(@Hour+as+Varchar)+%2B+'':''+%2B+Cast(@Minute+as+Varchar)+%2B+'':''+%2B+++++Cast(@Seconds+as+Varchar)

Return+(@Elapsed)
End'
)|code-block|syntax|javascript|3067226|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlserverbuddy.blogspot.com/2011/06/hour-minute-second-between-two-date.html^0|1I|4|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

So this isn't my answer but I just found this while searching around online for a question like this as well. This guy set up a procedure to calculate hours, minutes and seconds. The <a href="http://sqlserverbuddy.blogspot.com/2011/06/hour-minute-second-between-two-date.html" rel="nofollow">link</a> and the code:

<pre><code>--Creating Function
If OBJECT_ID('UFN_HourMinuteSecond') Is Not Null
Drop Function dbo.UFN_HourMinuteSecond
Go
Exec(
'Create Function dbo.UFN_HourMinuteSecond
(
@StartDateTime DateTime,
@EndDateTime DateTime
) Returns Varchar(10) 
As
Begin

Declare @Seconds Int,
@Minute Int,
@Hour Int,
@Elapsed Varchar(10)

Select @Seconds = ABS(DateDiff(SECOND ,@StartDateTime,@EndDateTime))

If @Seconds &gt;= 60 
Begin
select @Minute = @Seconds/60
select @Seconds = @Seconds%60

If @Minute &gt;= 60
begin
select @hour = @Minute/60
select @Minute = @Minute%60
end

Else
Goto Final 
End

Final:
Select @Hour = Isnull(@Hour,0), @Minute = IsNull(@Minute,0), @Seconds = IsNull(@Seconds,0)
select @Elapsed = Cast(@Hour as Varchar) + '':'' + Cast(@Minute as Varchar) + '':'' + Cast(@Seconds as Varchar)

Return (@Elapsed)
End'
)
</code></pre>

blocks|key|3280342|text|declare+@dt1+datetime='2012/06/13+08:11:12',+@dt2+datetime='2012/06/12+02:11:12'

select+CAST((@dt2-@dt1)+as+time(0))|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3280343|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>declare @dt1 datetime='2012/06/13 08:11:12', @dt2 datetime='2012/06/12 02:11:12'

select CAST((@dt2-@dt1) as time(0))
</code></pre>

blocks|key|3067296|text|打印DATEDIFF(秒，'2010-01-22+15:29:55.090'，'2010-01-22+15:30:09.153')|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3067297|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

PRINT DATEDIFF(second,'2010-01-22 15:29:55.090','2010-01-22 15:30:09.153')

blocks|key|322030|text|select
datediff(millisecond,'2010-01-22+15:29:55.090','2010-01-22+15:30:09.153')+/+1000.0+as+Secs

result:
Secs
14.063|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|322031|只是觉得我应该提一下。|unstyled|322032|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>select
datediff(millisecond,'2010-01-22 15:29:55.090','2010-01-22 15:30:09.153') / 1000.0 as Secs

result:
Secs
14.063
</code></pre>

Just thought I'd mention it.

blocks|key|320130|text|CREATE+FUNCTION+getDateDiffHours(@fdate作为datetime，@tdate作为datetime)返回varchar+(50)作为BEGIN+DECLARE+@cnt+int+DECLARE+@cntDate+datetime+DECLARE+@dayDiff+int+DECLARE+@dayDiffWk+int+DECLARE+@hrsDiff+decimal(18)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|320131|DECLARE+@markerFDate+datetime
DECLARE+@markerTDate+datetime

DECLARE+@fTime+int
DECLARE+@tTime+int+


DECLARE+@nfTime+varchar(8)
DECLARE+@ntTime+varchar(8)

DECLARE+@nfdate+datetime
DECLARE+@ntdate+datetime

-------------------------------------
--DECLARE+@fdate+datetime
--DECLARE+@tdate+datetime

--SET+@fdate+=+'2005-04-18+00:00:00.000'
--SET+@tdate+=+'2005-08-26+15:06:07.030'
-------------------------------------

DECLARE+@tempdate+datetime

--setting+weekends
SET+@fdate+=+dbo.getVDate(@fdate)
SET+@tdate+=+dbo.getVDate(@tdate)
--RETURN+@fdate+

SET+@fTime+=+datepart(hh,@fdate)
SET+@tTime+=+datepart(hh,@tdate)
--RETURN+@fTime+
if+datediff(hour,@fdate,+@tdate)+<=+9

++++++++RETURN(convert(varchar(50),0)+%2B+'+Days+'+%2B+convert(varchar(50),datediff(hour,@fdate,+@tdate)))++%2B+'+Hours'
else
--setting+working+hours
SET+@nfTime+=+dbo.getV00(convert(varchar(2),datepart(hh,@fdate)))+%2B+':'+%2Bdbo.getV00(convert(varchar(2),datepart(mi,@fdate)))+%2B+':'%2B++dbo.getV00(convert(varchar(2),datepart(ss,@fdate)))
SET+@ntTime+=+dbo.getV00(convert(varchar(2),datepart(hh,@tdate)))+%2B+':'+%2Bdbo.getV00(convert(varchar(2),datepart(mi,@tdate)))+%2B+':'%2B++dbo.getV00(convert(varchar(2),datepart(ss,@tdate)))

IF+@fTime+>+17+
begin
++++set+@nfTime+=+'17:00:00'
end+
else
begin
++++IF+@fTime+<+8+
++++++++set+@nfTime+=+'08:00:00'
end+

IF+@tTime+>+17+
begin
++++set+@ntTime+=+'17:00:00'
end+
else
begin
++++IF+@tTime+<+8+
++++++++set+@ntTime+=+'08:00:00'
end+



--+used+for+working+out+whole+days

SET+@nfdate+=+dateadd(day,1,@fdate)+

SET+@ntdate+=+@tdate
SET+@nfdate+=+convert(varchar,datepart(yyyy,@nfdate))+%2B+'-'+%2B+convert(varchar,datepart(mm,@nfdate))+%2B+'-'+%2B+convert(varchar,datepart(dd,@nfdate))
SET+@ntdate+=+convert(varchar,datepart(yyyy,@ntdate))+%2B+'-'+%2B+convert(varchar,datepart(mm,@ntdate))+%2B+'-'+%2B+convert(varchar,datepart(dd,@ntdate))
SET+@cnt+=+0
SET+@dayDiff+=+0+
SET+@cntDate+=+@nfdate
SET+@dayDiffWk+=+convert(decimal(18,2),@ntdate-@nfdate)

--select+@nfdate,@ntdate

WHILE+@cnt+<+@dayDiffWk
BEGIN+++
++++IF+(NOT+DATENAME(dw,+@cntDate)+=+'Saturday')+AND+(NOT+DATENAME(dw,+@cntDate)+=+'Sunday')
++++BEGIN+
++++++++SET+@dayDiff+=+@dayDiff+%2B+1
++++END+
++++SET+@cntDate+=+dateadd(day,1,@cntDate)
++++SET+@cnt+=+@cnt+%2B+1
END+

--SET+@dayDiff+=+convert(decimal(18,2),@ntdate-@nfdate)+--datediff(day,@nfdate,@ntdate)
--SELECT+@dayDiff

set+@fdate+=+convert(varchar,datepart(yyyy,@fdate))+%2B+'-'+%2B+convert(varchar,datepart(mm,@fdate))+%2B+'-'+%2B+convert(varchar,datepart(dd,@fdate))+%2B+'+'+%2B+@nfTime
set+@tdate+=+convert(varchar,datepart(yyyy,@tdate))+%2B+'-'+%2B+convert(varchar,datepart(mm,@tdate))+%2B+'-'+%2B+convert(varchar,datepart(dd,@tdate))+%2B+'+'+%2B+@ntTime

set+@markerFDate+=+convert(varchar,datepart(yyyy,@fdate))+%2B+'-'+%2B+convert(varchar,datepart(mm,@fdate))+%2B+'-'+%2B+convert(varchar,datepart(dd,@fdate))+%2B+'+'+%2B+'17:00:00'
set+@markerTDate+=+convert(varchar,datepart(yyyy,@tdate))+%2B+'-'+%2B+convert(varchar,datepart(mm,@tdate))+%2B+'-'+%2B+convert(varchar,datepart(dd,@tdate))+%2B+'+'+%2B+'08:00:00'

--select+@fdate,@tdate
--select+@markerFDate,@markerTDate

set+@hrsDiff+=+convert(decimal(18,2),datediff(hh,@fdate,@markerFDate))

--select+@hrsDiff
set+@hrsDiff+=+@hrsDiff+%2B++convert(int,datediff(hh,@markerTDate,@tdate))

--select+@fdate,@tdate++

IF+convert(varchar,datepart(yyyy,@fdate))+%2B+'-'+%2B+convert(varchar,datepart(mm,@fdate))+%2B+'-'+%2B+convert(varchar,datepart(dd,@fdate))+=+convert(varchar,datepart(yyyy,@tdate))+%2B+'-'+%2B+convert(varchar,datepart(mm,@tdate))+%2B+'-'+%2B+convert(varchar,datepart(dd,@tdate))++
BEGIN
++++--SET+@hrsDiff+=+@hrsDiff+-+9
++++Set+@hrsdiff+=+datediff(hour,@fdate,@tdate)
END+

--select+FLOOR((@hrsDiff+/+9))

IF+(@hrsDiff+/+9)+>+0+
BEGIN
++++SET+@dayDiff+=+@dayDiff+%2B+FLOOR(@hrsDiff+/+9)
++++SET+@hrsDiff+=+@hrsDiff+-+FLOOR(@hrsDiff+/+9)*9
END+

--select+convert(varchar(50),@dayDiff)+%2B+'+Days+'+%2B+convert(varchar(50),@hrsDiff)+++%2B+'+Hours'

RETURN(convert(varchar(50),@dayDiff)+%2B+'+Days+'+%2B+convert(varchar(50),@hrsDiff))++++%2B+'+Hours'|code-block|syntax|javascript|320132|结束|320133|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

CREATE FUNCTION getDateDiffHours(@fdate AS datetime,@tdate as datetime)
RETURNS varchar (50)
AS
BEGIN
 DECLARE @cnt int
 DECLARE @cntDate datetime
 DECLARE @dayDiff int
 DECLARE @dayDiffWk int
 DECLARE @hrsDiff decimal(18)

<pre><code>DECLARE @markerFDate datetime
DECLARE @markerTDate datetime

DECLARE @fTime int
DECLARE @tTime int 


DECLARE @nfTime varchar(8)
DECLARE @ntTime varchar(8)

DECLARE @nfdate datetime
DECLARE @ntdate datetime

-------------------------------------
--DECLARE @fdate datetime
--DECLARE @tdate datetime

--SET @fdate = '2005-04-18 00:00:00.000'
--SET @tdate = '2005-08-26 15:06:07.030'
-------------------------------------

DECLARE @tempdate datetime

--setting weekends
SET @fdate = dbo.getVDate(@fdate)
SET @tdate = dbo.getVDate(@tdate)
--RETURN @fdate 

SET @fTime = datepart(hh,@fdate)
SET @tTime = datepart(hh,@tdate)
--RETURN @fTime 
if datediff(hour,@fdate, @tdate) &lt;= 9

 RETURN(convert(varchar(50),0) + ' Days ' + convert(varchar(50),datediff(hour,@fdate, @tdate))) + ' Hours'
else
--setting working hours
SET @nfTime = dbo.getV00(convert(varchar(2),datepart(hh,@fdate))) + ':' +dbo.getV00(convert(varchar(2),datepart(mi,@fdate))) + ':'+ dbo.getV00(convert(varchar(2),datepart(ss,@fdate)))
SET @ntTime = dbo.getV00(convert(varchar(2),datepart(hh,@tdate))) + ':' +dbo.getV00(convert(varchar(2),datepart(mi,@tdate))) + ':'+ dbo.getV00(convert(varchar(2),datepart(ss,@tdate)))

IF @fTime &gt; 17 
begin
 set @nfTime = '17:00:00'
end 
else
begin
 IF @fTime &lt; 8 
 set @nfTime = '08:00:00'
end 

IF @tTime &gt; 17 
begin
 set @ntTime = '17:00:00'
end 
else
begin
 IF @tTime &lt; 8 
 set @ntTime = '08:00:00'
end 



-- used for working out whole days

SET @nfdate = dateadd(day,1,@fdate) 

SET @ntdate = @tdate
SET @nfdate = convert(varchar,datepart(yyyy,@nfdate)) + '-' + convert(varchar,datepart(mm,@nfdate)) + '-' + convert(varchar,datepart(dd,@nfdate))
SET @ntdate = convert(varchar,datepart(yyyy,@ntdate)) + '-' + convert(varchar,datepart(mm,@ntdate)) + '-' + convert(varchar,datepart(dd,@ntdate))
SET @cnt = 0
SET @dayDiff = 0 
SET @cntDate = @nfdate
SET @dayDiffWk = convert(decimal(18,2),@ntdate-@nfdate)

--select @nfdate,@ntdate

WHILE @cnt &lt; @dayDiffWk
BEGIN 
 IF (NOT DATENAME(dw, @cntDate) = 'Saturday') AND (NOT DATENAME(dw, @cntDate) = 'Sunday')
 BEGIN 
 SET @dayDiff = @dayDiff + 1
 END 
 SET @cntDate = dateadd(day,1,@cntDate)
 SET @cnt = @cnt + 1
END 

--SET @dayDiff = convert(decimal(18,2),@ntdate-@nfdate) --datediff(day,@nfdate,@ntdate)
--SELECT @dayDiff

set @fdate = convert(varchar,datepart(yyyy,@fdate)) + '-' + convert(varchar,datepart(mm,@fdate)) + '-' + convert(varchar,datepart(dd,@fdate)) + ' ' + @nfTime
set @tdate = convert(varchar,datepart(yyyy,@tdate)) + '-' + convert(varchar,datepart(mm,@tdate)) + '-' + convert(varchar,datepart(dd,@tdate)) + ' ' + @ntTime

set @markerFDate = convert(varchar,datepart(yyyy,@fdate)) + '-' + convert(varchar,datepart(mm,@fdate)) + '-' + convert(varchar,datepart(dd,@fdate)) + ' ' + '17:00:00'
set @markerTDate = convert(varchar,datepart(yyyy,@tdate)) + '-' + convert(varchar,datepart(mm,@tdate)) + '-' + convert(varchar,datepart(dd,@tdate)) + ' ' + '08:00:00'

--select @fdate,@tdate
--select @markerFDate,@markerTDate

set @hrsDiff = convert(decimal(18,2),datediff(hh,@fdate,@markerFDate))

--select @hrsDiff
set @hrsDiff = @hrsDiff + convert(int,datediff(hh,@markerTDate,@tdate))

--select @fdate,@tdate 

IF convert(varchar,datepart(yyyy,@fdate)) + '-' + convert(varchar,datepart(mm,@fdate)) + '-' + convert(varchar,datepart(dd,@fdate)) = convert(varchar,datepart(yyyy,@tdate)) + '-' + convert(varchar,datepart(mm,@tdate)) + '-' + convert(varchar,datepart(dd,@tdate)) 
BEGIN
 --SET @hrsDiff = @hrsDiff - 9
 Set @hrsdiff = datediff(hour,@fdate,@tdate)
END 

--select FLOOR((@hrsDiff / 9))

IF (@hrsDiff / 9) &gt; 0 
BEGIN
 SET @dayDiff = @dayDiff + FLOOR(@hrsDiff / 9)
 SET @hrsDiff = @hrsDiff - FLOOR(@hrsDiff / 9)*9
END 

--select convert(varchar(50),@dayDiff) + ' Days ' + convert(varchar(50),@hrsDiff) + ' Hours'

RETURN(convert(varchar(50),@dayDiff) + ' Days ' + convert(varchar(50),@hrsDiff)) + ' Hours'
</code></pre>

END

blocks|key|320142|text|Sol-1:|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|320143|select+
++StartTime
++,+EndTime
++,+CONVERT(NVARCHAR,(EndTime-StartTime),+108)+as+TimeDiff+
from+
++[YourTable]|code-block|syntax|javascript|320144|Sol-2:|320145|select+
++StartTime
++,+EndTime
++,+DATEDIFF(hh,+StartTime,+EndTime)
++,+DATEDIFF(mi,+StartTime,+EndTime)+%25+60+
from+
++[YourTable]|320146|Sol-3:|320147|select+
++DATEPART(hour,[EndTime]-[StartTime])
++,+DATEPART(minute,[EndTime]-[StartTime])+
from+
++[YourTable]|320148|Datepart运行得最好|320149|entityMap^0|0|6|0|0|0|6|0|0|0|6|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|Z|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|10|8|@$9|11|A|12|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|13|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|14|8|@$9|15|A|16|B|C]]|D|@]|E|$]]|$1|Q|3|R|5|H|7|17|8|@]|D|@]|E|$I|J]]|$1|S|3|T|5|6|7|18|8|@]|D|@]|E|$]]|$1|U|3|-4|5|6|7|19|8|@]|D|@]|E|$]]]|V|$]]

Sol-1: 

<pre><code>select 
 StartTime
 , EndTime
 , CONVERT(NVARCHAR,(EndTime-StartTime), 108) as TimeDiff 
from 
 [YourTable]
</code></pre>

Sol-2: 

<pre><code>select 
 StartTime
 , EndTime
 , DATEDIFF(hh, StartTime, EndTime)
 , DATEDIFF(mi, StartTime, EndTime) % 60 
from 
 [YourTable]
</code></pre>

Sol-3: 

<pre><code>select 
 DATEPART(hour,[EndTime]-[StartTime])
 , DATEPART(minute,[EndTime]-[StartTime]) 
from 
 [YourTable]
</code></pre>

Datepart works the best

blocks|key|319167|text|请检查下面的技巧，找出两个日期之间的日期差异|type|unstyled|depth|inlineStyleRanges|entityRanges|data|319168|+DATEDIFF(DAY,ordr.DocDate,RDR1.U_ProgDate)+datedifff|code-block|syntax|javascript|319169|在这里你可以根据你的需求改变你想要的日期、月份、年份或时间的不同。|319170|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Please check below trick to find the date difference between two dates 

<pre><code> DATEDIFF(DAY,ordr.DocDate,RDR1.U_ProgDate) datedifff
</code></pre>

where you can change according your requirement as you want difference of days or month or year or time.

blocks|key|322117|text|对我来说，这是完美的转换(varchar(8)，DATEADD(SECOND，DATEDIFF(SECOND，LogInTime，LogOutTime),0),114)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|322118|输出是HH:MM:SS，这在我的例子中显示得很准确。|322119|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

For Me This worked Perfectly
Convert(varchar(8),DATEADD(SECOND,DATEDIFF(SECOND,LogInTime,LogOutTime),0),114)

and the Output is 
HH:MM:SS which is shown accurately in my case.

blocks|key|3067484|text|请尝试|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3067485|DECLARE+@articleDT+DATETIME;
DECLARE+@nowDate+DATETIME;
+
--+Time+of+the+ARTICLE+created
SET+@articleDT+=+'2012-04-01+08:10:16';
+
--+Simulation+of+NOW+datetime
--+(in+real+world+you+would+probably+use+GETDATE())
SET+@nowDate+=+'2012-04-10+11:35:36';
+
--+Created+9+days+ago.
SELECT+'Created+'+%2B+CAST(DATEDIFF(day,+@articleDT,+@nowDate)+AS+NVARCHAR(50))+%2B+'+days+ago.';
+
--+Created+1+weeks,+2+days,+3+hours,+25+minutes+and+20+seconds+ago.
SELECT+'Created+'
++++%2B+CAST(DATEDIFF(second,+@articleDT,+@nowDate)+/+60+/+60+/+24+/+7+AS+NVARCHAR(50))+%2B+'+weeks,+'
++++%2B+CAST(DATEDIFF(second,+@articleDT,+@nowDate)+/+60+/+60+/+24+%25+7+AS+NVARCHAR(50))+%2B+'+days,+'
++++%2B+CAST(DATEDIFF(second,+@articleDT,+@nowDate)+/+60+/+60+%25+24++AS+NVARCHAR(50))+%2B+'+hours,+'
++++%2B+CAST(DATEDIFF(second,+@articleDT,+@nowDate)+/+60+%25+60+AS+NVARCHAR(50))+%2B+'+minutes+and+'
++++%2B+CAST(DATEDIFF(second,+@articleDT,+@nowDate)+%25+60+AS+NVARCHAR(50))+%2B+'+seconds+ago.';|code-block|syntax|javascript|3067486|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Please try
<pre><code>DECLARE @articleDT DATETIME;
DECLARE @nowDate DATETIME;
 
-- Time of the ARTICLE created
SET @articleDT = '2012-04-01 08:10:16';
 
-- Simulation of NOW datetime
-- (in real world you would probably use GETDATE())
SET @nowDate = '2012-04-10 11:35:36';
 
-- Created 9 days ago.
SELECT 'Created ' + CAST(DATEDIFF(day, @articleDT, @nowDate) AS NVARCHAR(50)) + ' days ago.';
 
-- Created 1 weeks, 2 days, 3 hours, 25 minutes and 20 seconds ago.
SELECT 'Created '
 + CAST(DATEDIFF(second, @articleDT, @nowDate) / 60 / 60 / 24 / 7 AS NVARCHAR(50)) + ' weeks, '
 + CAST(DATEDIFF(second, @articleDT, @nowDate) / 60 / 60 / 24 % 7 AS NVARCHAR(50)) + ' days, '
 + CAST(DATEDIFF(second, @articleDT, @nowDate) / 60 / 60 % 24 AS NVARCHAR(50)) + ' hours, '
 + CAST(DATEDIFF(second, @articleDT, @nowDate) / 60 % 60 AS NVARCHAR(50)) + ' minutes and '
 + CAST(DATEDIFF(second, @articleDT, @nowDate) % 60 AS NVARCHAR(50)) + ' seconds ago.';
</code></pre>

blocks|key|3280243|text|在联机丛书上查看DateDiff。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3280244|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Check DateDiff out on Books Online.

Is there any way to take the difference between two <code>datetime</code> in sql server?

For example, my dates are

<ol>
<li><code>2010-01-22 15:29:55.090</code></li>
<li><code>2010-01-22 15:30:09.153</code></li>
</ol>

So, the result should be <code>14.063 seconds</code>.

Difference of two date time in sql server

SQL Server

在sql server中有没有办法区分两个datetime？例如，我的日期是2010-01-22 15:29:55.0902010-01-22 15:30:09.153因此，结果应该是14.063 seconds。

问sql server中两个日期时间的差异
EN

回答 21

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问sql server中两个日期时间的差异EN

回答 21

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问sql server中两个日期时间的差异
EN