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

Question

提问by Jibu P C_Adoor

Is there any way to take the difference between two datetimein sql server?

有什么办法可以区分datetimesql server中的两者之间的区别吗？

For example, my dates are

例如，我的日期是

2010-01-22 15:29:55.090
2010-01-22 15:30:09.153

2010-01-22 15:29:55.090
2010-01-22 15:30:09.153

So, the result should be 14.063 seconds.

所以，结果应该是14.063 seconds。

Answer 1

回答by Andrew

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.

只是添加关于 DateDiff 的警告，它计算您通过指定为单位的边界的次数，因此如果您正在寻找精确的时间跨度，则可能会出现问题。例如

select datediff (m, '20100131', '20100201')

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.

给出 1 的答案，因为它从一月到二月跨越了边界，所以即使跨度是 2 天，datediff 也会返回值 1 - 它跨越了 1 个日期边界。

select datediff(mi, '2010-01-22 15:29:55.090' , '2010-01-22 15:30:09.153')

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.

再次给出值 1，它超过了分钟边界一次，因此即使是大约 14 秒，当使用分钟作为单位时，它也会作为一分钟返回。

Answer 2

回答by gbn

SELECT DATEDIFF (MyUnits, '2010-01-22 15:29:55.090', '2010-01-22 15:30:09.153')

Substitute "MyUnits" based on DATEDIFF on MSDN

基于MSDN上的DATEDIFF替换“MyUnits”

Answer 3

回答by Quassnoi

SELECT  DATEDIFF(day, '2010-01-22 15:29:55.090', '2010-01-22 15:30:09.153')

Replace daywith other units you want to get the difference in, like second, minuteetc.

更换day与其他单位要获取的差异，如second，minute等。

Answer 4

回答by lynx_74

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

我可以提到 MS SQL Server 的四个非常有用的重要功能：

1) The function DATEDIFF()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):

1) 函数DATEDIFF()负责计算两个日期之间的差异，结果可能是“年季月日年日周小时分秒毫秒微秒纳秒”，在第一个参数（datepart）上指定：

select datediff(day,'1997-10-07','2011-09-11')

2) You can use the function GETDATE()to get the actual time and calculate differences of some date and actual date:

2) 您可以使用函数GETDATE()获取实际时间并计算某些日期与实际日期的差异：

select datediff(day,'1997-10-07', getdate() )

3) Another important function is DATEADD(), used to convert some value in datetime using the same datepartof the datediff, that you can add (with positive values) or substract (with negative values) to one base date:

3）另一个重要的函数是DATEADD()，用于使用datediff的相同日期部分转换日期时间中的某些值，您可以将其添加（使用正值）或减去（使用负值）到一个基准日期：

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

4) The function CONVERT()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:

4) 函数CONVERT()用于按照您的需要格式化日期，它不是参数函数，但您可以使用部分结果来格式化您需要的结果：

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

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:

DATETIME 冷以秒计算，混合这四个函数的一个有趣结果是显示两个日期之间的格式化差异嗯小时、分钟和秒 ( hh:mm:ss)：

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)

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

... 结果是00:10:38（638s = 600s + 38s = 10 分 38 秒）

Another example:

另一个例子：

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

Answer 5

回答by rchacko

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

我试过这种方式，它奏效了。我使用的是 SQL Server 2016 版

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

Different DATEDIFF Functions are:

不同的 DATEDIFF 函数是：

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');

Ref: https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017

参考：https: //docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view =sql-server- 2017

Answer 6

回答by Fandango68

Ok we all know the answer involves DATEDIFF(). 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 DATETIMEvalues?

好的，我们都知道答案涉及DATEDIFF()。但这只会给您带来可能获得的结果的一半。如果您想以人类可读的格式获得两个DATETIME值之间的分钟和秒结果怎么办？

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

的CONVERT()，DATEADD()当然DATEDIFF()功能是完美的一个更易读的结果，你的客户端可以使用，而不是数量。

i.e.

IE

CONVERT(varchar(5), DATEADD(minute, DATEDIFF(MINUTE, date1, date2), 0), 114)

This will give you something like:

这会给你类似的东西：

HH:MM

时:分

If you want more precision, just increase the VARCHAR().

如果您想要更高的精度，只需增加VARCHAR().

CONVERT(varchar(12), DATEADD(minute, DATEDIFF(MINUTE, date1, date2), 0), 114)

HH:MM.SS.MS

时：MM.SS.MS

Answer 7

回答by Prateek

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

以下查询应提供您正在寻找的确切内容。

select datediff(second, '2010-01-22 15:29:55.090' , '2010-01-22 15:30:09.153')

Here is the link from MSDN for what all you can do with datediff function . https://msdn.microsoft.com/en-us/library/ms189794.aspx

这是来自 MSDN 的链接，了解您可以使用 datediff 函数做什么。 https://msdn.microsoft.com/en-us/library/ms189794.aspx

Answer 8

回答by Prateek

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":

查看日期差异的方法有很多种，比较日期/时间的方法更多。这是我用来获取格式为“HH:MM:SS”的两个日期之间的差异的方法：

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)

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".

我将它用于计算列，但您可以轻松地将其重写为 UDF 或查询计算。请注意，此逻辑会向下舍入小数秒；00:00.00 到 00:00.999 被视为零秒，并显示为“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:

如果您预计周期可能会超过几天，此代码会在需要时切换为 D:HH:MM:SS 格式：

ElapsedTime AS
    CASE WHEN DATEDIFF(S, StartDate, EndDate) >= 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

Answer 9

回答by RedFilter

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.

在 SQL Server 内部，日期存储为 2 个整数。第一个整数是基准日期 (1900/01/01) 之前或之后的日期数。第二个整数存储午夜之后的时钟滴答数，每个滴答声是 1/300 秒。

More info here

更多信息在这里

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.,

因此，我经常发现比较日期的最简单方法是简单地减去它们。这处理了我 90% 的用例。例如，

select date1, date2, date2 - date1 as DifferenceInDays
from MyTable
...

When I need an answer in units other than days, I will use DateDiff.

当我需要以天为单位的答案时，我将使用DateDiff。

Answer 10

回答by Abhishek Kanrar

SELECT DATEDIFF(yyyy, '2011/08/25', '2017/08/25') AS DateDiff

It's gives you difference between two dates in Year

它为您提供了年份中两个日期之间的差异

Here (2017-2011)=6 as a result

这里 (2017-2011)=6 结果

Syntax:

句法：

DATEDIFF(interval, date1, date2)

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

提问by Jibu P C_Adoor

回答by Andrew

回答by gbn

回答by Quassnoi

回答by lynx_74

回答by rchacko

回答by Fandango68

回答by Prateek

回答by Prateek

回答by RedFilter

回答by Abhishek Kanrar

相关推荐

最近更新

标签

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

提问by Jibu P C_Adoor

回答by Andrew

回答by gbn

回答by Quassnoi

回答by lynx_74

回答by rchacko

回答by Fandango68

回答by Prateek

回答by Prateek

回答by RedFilter

回答by Abhishek Kanrar

相关推荐

Spark/scala 中的 SQL 查询大小超过 Integer.MAX_VALUE

是否可以在 Sql Server Management Studio Express 中自动缩进查询？

SQL 什么是数据库中的半连接？

SQL 如何在 Linux 上比较两个 SQLite 数据库

相关推荐

最近更新

标签