Geek Answers Handbook

How to select all watches 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';

I want to create a select statement that will return every hour between @minDateTime and @maxDateTime as follows (there is no table to select. I'm not looking for where the sentence is!):

 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
+7
sql sql-server tsql
source share
2 answers

Try it. Use the Recursive 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)

The Dates_CTE query Dates_CTE has a Common Expression Table , the base record for CTE is output by the first SQL query before UNION ALL . The result of the query gives you the Minimum date .

The second query after UNION ALL is run again to get the results. This process is recursive and will continue until the Dates are less than @maxDateTime .

+9
source share

Here is another way: Tally Table :

 DECLARE @minDateTime DATETIME; DECLARE @maxDateTime DATETIME; SET @minDateTime = '2014-01-13 02:00:00'; SET @maxDateTime = '2014-12-31 14:00:00'; DECLARE @hrsDiff INT; SELECT @hrsDiff = DATEDIFF(HH, @minDateTime, @maxDateTime); WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ),--10E+1 or 10 rows E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max Tally(N) AS(SELECT row_number() over(order by (select null)) from E4) -- Numbered rrow SELECT @minDateTime UNION ALL SELECT DATEADD(HH, N, @minDateTime) FROM Tally WHERE N <= @hrsDiff
+2
source share

More articles:


All Articles