如何选择两个日期之间的所有小时数? [英] How to select all hours between two dates?

问题描述

declare @minDateTime as datetime;
declare @maxDateTime as datetime;

set @minDateTime = '2014-01-13 02:00:00';
set @maxDateTime = '2014-12-31 14:00:00';

我希望创建一个 select 语句，该语句将在 @minDateTime 和 @maxDateTime 之间每小时返回一次，如下所示(没有可供选择的表.我不是在寻找 where 子句！):

I am looking to create a select statement that would return every hour between @minDateTime and @maxDateTime as follows (there is no table to select from. I am not looking for where clause !):

2014-01-13 02:00:00
2014-01-13 03:00:00
2014-01-13 04:00:00
...
2014-12-31 12:00:00
2014-12-31 13:00:00
2014-12-31 14:00:00

推荐答案

试试这个.使用 递归 CTE.

DECLARE @minDateTime AS DATETIME;
DECLARE @maxDateTime AS DATETIME;

SET @minDateTime = '2014-01-13 02:00:00';
SET @maxDateTime = '2014-12-31 14:00:00';

;
WITH Dates_CTE
     AS (SELECT @minDateTime AS Dates
         UNION ALL
         SELECT Dateadd(hh, 1, Dates)
         FROM   Dates_CTE
         WHERE  Dates < @maxDateTime)
SELECT *
FROM   Dates_CTE
OPTION (MAXRECURSION 0) 

在上面的查询中Dates_CTE是一个Common Expression Table，CTE的基础记录是由CTE之前的第一个sql查询导出的代码>联合所有.查询结果为您提供最小日期.

In the above query Dates_CTE is a Common Expression Table, the base record for the CTE is derived by the first sql query before UNION ALL. The result of the query gives you the Minimum date.

重复执行UNION ALL 后的第二次查询，得到结果.这个过程是递归，并且会一直持续到日期小于@maxDateTime.

Second query after UNION ALL is executed repeatedly to get results. This process is recursive and will continue till the Dates is less than @maxDateTime.

这篇关于如何选择两个日期之间的所有小时数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！