How to choose continuous date in sql

Question

Is there a function to check for a continuous date. I had a problem working with this question below:

My table has a datetime column with the following data:

 ---------- 2015-03-11 2015-03-12 2015-03-13 2015-03-16 

The indicated start date is as 2015-3-11 and the end date is as 2015-3-17 . I want the result to be as follows:

 ---------- 2015-03-11 2015-03-12 2015-03-13 

Can anyone suggest something?

Answer 1

I think this is some variation of the Grouping of islands related dates . This can be done using ROW_NUMBER() :

SQL Fiddle

 CREATE TABLE Test( tDate DATETIME ) INSERT INTO Test VALUES ('20150311'), ('20150312'), ('20150313'), ('20150316'); DECLARE @startDate DATE = '20150311' DECLARE @endDate DATE = '20150317' ;WITH Cte AS( SELECT *, RN = DATEADD(DD, - (ROW_NUMBER() OVER(ORDER BY tDATE) - 1), tDate) FROM Test WHERE tDate >= @startDate AND tDate < DATEADD(DAY, 1, @endDate) ) SELECT CAST(tDate AS DATE) FROM CTE WHERE RN = @startDate 

RESULT

 |------------| | 2015-03-11 | | 2015-03-12 | | 2015-03-13 | 

---

Here is the version of SQL Server 2005:

SQL Fiddle

 DECLARE @startDate DATETIME DECLARE @endDate DATETIME SET @startDate = '20150311' SET @endDate = '20150317' ;WITH Cte AS( SELECT *, RN = DATEADD(DD, -(ROW_NUMBER() OVER(ORDER BY tDATE)-1), tDate) FROM Test WHERE tDate >= @startDate AND tDate < DATEADD(DAY, 1, @endDate) ) SELECT CONVERT(VARCHAR(10), tDate, 121) FROM CTE WHERE RN = @startDate 

Answer 2

For MSSQL 2012 . This will return MAX contiguous groups:

 DECLARE @t TABLE(d DATE) INSERT INTO @t VALUES ('20150311'), ('20150312'), ('20150313'), ('20150316') ;WITH c1 AS(SELECT d, IIF(DATEDIFF(dd,LAG(d, 1, DATEADD(dd, -1, d)) OVER(ORDER BY d), d) = 1, 0, 1) AS n FROM @t), c2 AS(SELECT d, SUM(n) OVER(ORDER BY d) AS n FROM c1) SELECT TOP 1 WITH TIES MIN(d) AS StartDate, MAX(d) AS EndDate, COUNT(*) AS DayCount FROM c2 GROUP BY n ORDER BY DayCount desc 

Conclusion:

 StartDate EndDate DayCount 2015-03-11 2015-03-13 3 

For

 ('20150311'), ('20150312'), ('20150313'), ('20150316'), ('20150317'), ('20150318'), ('20150319'), ('20150320') 

Conclusion:

 StartDate EndDate DayCount 2015-03-16 2015-03-20 5 

Apply filtering in c1 CTE :

 c1 AS(SELECT d, IIF(DATEDIFF(dd,LAG(d, 1, DATEADD(dd, -1, d)) OVER(ORDER BY d), d) = 1, 0, 1) AS n FROM @t WHERE d BETWEEN '20150311' AND '20150320'), 

For MSSQL 2008 :

 ;WITH c1 AS(SELECT d, (SELECT MAX(d) FROM @t it WHERE it.d < ot.d) AS pd FROM @t ot), c2 AS(SELECT d, CASE WHEN DATEDIFF(dd,ISNULL(pd, DATEADD(dd, -1, d)), d) = 1 THEN 0 ELSE 1 END AS n FROM c1), c3 AS(SELECT d, (SELECT SUM(n) FROM c2 ci WHERE ci.d <= co.d) AS n FROM c2 co) SELECT TOP 1 WITH TIES MIN(d) AS StartDate, MAX(d) AS EndDate, COUNT(*) AS DayCount FROM c3 GROUP BY n ORDER BY DayCount desc 

Answer 3

you don’t need to declare a start date or end date, as other answers say, you need a row_num function with datediff :

 create table DateFragTest (cDate date); insert into DateFragTest values ('2015-3-11'), ('2015-3-12'), ('2015-3-13'), ('2015-3-16') with cte as (select cDate, row_number() over (order by cDate ) as rn from DateFragTest) select cDate from cte t1 where datediff(day, (select cDate from cte t2 where t2.rn=t1.rn+1), t1.cDate)<>1 

Conclusion:

 cDate 2015-03-11 2015-03-12 2015-03-13 

SQLFIDDLE DEMO

Answer 4

For sql server 2012 -

 WITH cte AS ( SELECT [datex] , lead([datex]) OVER ( ORDER BY [datex]) lead_datex , datediff(dd,[datex],lead([datex]) OVER ( ORDER BY [datex]) ) AS diff FROM [dbo].[datex] ) SELECT c.[datex] FROM [cte] AS c WHERE diff >=1 

Answer 5

Use BETWEEN

The request will look like this:

 SELECT * FROM your_table_name WHERE your_date_column_name BETWEEN '2015-3-11' AND '2015-3-13' 

Answer 6

(dt between x and y) or simply (dt >= x and dt <= y) .