-3

I need the sql query which gives result between all date by passing two dates. For example, suppose I sent 01/01/2015 and 10/01/2015

I need the date between passed dates

The result should be :

01/01/2015    
02/01/2015    
03/01/2015    
04/01/2015    
05/01/2015    
..        
10/01/2015

How can I achieve that?

Jamiec
  • 133,658
  • 13
  • 134
  • 193
Saura
  • 1
  • 2
  • Welcome to StackOverflow! I can give you a bank account number and a bid. Seriously: Please show some effort and share us what did you tried so far. StackOverflow is not a code gerenation service. – Pred Jan 13 '15 at 11:37
  • 1
    Why not create a [calendar table](http://www.made2mentor.com/2011/06/creating-a-date-tabledimension-for-sql-server-2008/), and select from that? – Tony Jan 13 '15 at 11:46

4 Answers4

1

You can use a recursive CTE for this:

DECLARE @startDate DATETIME = '2015-01-01',
    @endDate DATETIME = '2015-01-10'

;WITH dates AS(
 SELECT @startDate AS date
 UNION ALL
 SELECT DATEADD(dd,1,date) 
 FROM dates
 WHERE date<@endDate
)
SELECT * FROM dates
Jamiec
  • 133,658
  • 13
  • 134
  • 193
  • This is going to have poor performance. Check this http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-2 – Pரதீப் Jan 13 '15 at 11:43
  • I dont think the performance of generating 10 dates in a CTE is really going to bother anyone. Comparing the performance of generating 50,000 records with 10 is like comparing apples and oranges. – Jamiec Jan 13 '15 at 11:47
  • @Jamiec - In real time it might not be just 10. Anyway just a suggestion – Pரதீப் Jan 13 '15 at 11:59
0

Use a Numbers table to generate dates.

DECLARE @StartDate DATETIME = convert(date,'01/01/2015',103) -- First Calendar date to include in table
DECLARE @EndDate DATETIME = convert(date,'10/01/2015',103) --Last calendar date to include in the table


;WITH   E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
     cteTally(N)
     AS (SELECT Row_number()OVER ( ORDER BY N)
         FROM   E32),
     CalendarBase
     AS (SELECT CalendarDate = Dateadd(day, n - 1, @StartDate)
         FROM   cteTally
         WHERE  N <= Datediff(day, @StartDate, @EndDate + 1))
SELECT convert(varchar(30),CalendarDate ,103) as  CalendarDate
FROM   CalendarBase

Referred from this link

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
0

Try this : 

declare @strt date;
declare @end date;

select @strt = '20150101';
select @end = '20150110';

with dates as
(
select dt = dateadd(dd, 0, @strt)
where dateadd(dd, 1, @strt) <= @end
union all
select dateadd(dd, 1, dt)
from dates
where dateadd(dd, 1, dt) <= @end
)
select * from dates
Jignesh Khokhariya
  • 150
  • 1
  • 8
0

@Saurabh Dhakate

Use the below code:

declare @date1 date ='2015-01-10'
declare @date2 date  ='2015-01-01'
declare @lv_table table (datepart1 date)
declare @count int 
declare @count1 int = 1
set @count=(select day(@date1) )
print @count
while @count1<=@count
begin
insert into @lv_table
select dateadd(day,@count1,@date2)
set @count1=@count1+1
print @count1
end
select * from  @lv_table
karan arora
  • 176
  • 9