Blog

How to get all dates between date range in sql query

By Nipun Tomar Jun 05, 2008
This sql query will get all the dates beween given date range.

DECLARE @StartDate DATETIME,@EndDate DATETIME

SELECT @StartDate = '20080101',@EndDate = '20080930'

SELECT DATEADD(day, z.num, @StartDate)

FROM (

SELECT b10.i + b9.i + b8.i + b7.i + b6.i + b5.i + b4.i + b3.i + b2.i + b1.i + b0.i num

FROM (SELECT 0 i UNION ALL SELECT 1) b0

CROSS JOIN (SELECT 0 i UNION ALL SELECT 2) b1

CROSS JOIN (SELECT 0 i UNION ALL SELECT 4) b2

CROSS JOIN (SELECT 0 i UNION ALL SELECT 8) b3

CROSS JOIN (SELECT 0 i UNION ALL SELECT 16) b4

CROSS JOIN (SELECT 0 i UNION ALL SELECT 32) b5

CROSS JOIN (SELECT 0 i UNION ALL SELECT 64) b6

CROSS JOIN (SELECT 0 i UNION ALL SELECT 128) b7

CROSS JOIN (SELECT 0 i UNION ALL SELECT 256) b8

CROSS JOIN (SELECT 0 i UNION ALL SELECT 512) b9

CROSS JOIN (SELECT 0 i UNION ALL SELECT 1024) b10

) z

WHERE z.num <= DATEDIFF(day, @StartDate, @EndDate)

ORDER BY z.num