一种在t-SQL中选择两个日期之间的日期的方法

Say, I have two DATETIME2 columns and I need to select dates between the two. Example: 1/1/2012 08:00 and 1/1/2012 09:00 should produce 1/1/2012 08:30.

说,我有两个DATETIME2列,我需要选择两者之间的日期。示例:1/1/2012 08:00和1/1/2012 09:00应该生成1/1/2012 08:30。

I'm trying this:

我正在尝试这个:

SELECT CAST((CAST(dtOut AS float(53)) + 
       CAST(dtIn AS float(53))) / 2 AS DATETIME2) FROM t;

But I get an error that explicit conversion from DATETIME2 is not allowed.

但是我得到一个错误,即不允许从DATETIME2进行显式转换。

Any idea how to do it?

知道怎么做吗?

4 个解决方案

#1

DateDiff will find the difference between two dates.

DateDiff将找到两个日期之间的差异。

 select Dateadd(n, DATEDIFF(n, dtIn, dtOut)/2, dtIn) FROM t

As an aside, your method would work if the fields were datetime, not datetime2.

另外,如果字段是datetime而不是datetime2,那么您的方法将起作用。

#2

Try Out this.

试试这个。

  select Dateadd(n, DATEDIFF(n, '1/1/2012 08:00', '1/1/2012 09:00')/2, '1/1/2012 08:00');

#3

declare @d1 datetime2, @d2 datetime2
select @d1='1/1/2012 08:00',@d2='1/1/2012 09:00'
 select dateadd(minute,datediff(minute ,@d1,@d2)/2.0,@d1)

#4

select dateadd(SECOND,datediff(SECOND ,dtIn,dtOut)/2.0,dtIn)

选择dateadd(SECOND,datediff(SECOND,dtIn,dtOut)/2.0,dtIn)

#1