Blog

How to get all dates between date range in sql query

By Nipun Tomar Blogs | SQL Server 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

COMMENT USING

PREMIUM SPONSORS

Hire Mobile & Web Developer on demand. 100% satisfaction. Try for 1 week or Money Back. Local and remote developers available all over USA.

Latest Blogs