Blog

How to get all dates between date range in sql query

Posted by Nipun Tomar Blogs | SQL Server 2012 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

comments
COMMENT USING
PREMIUM SPONSORS
Infragistics is experts in technology and design, and passionate about helping you build highly performant and stylish applications that solve problems, deliver inspiration, and maximize results.
Nevron Diagram
SPONSORED BY
  • PDF reports have never been easier to create. With our included WYSIWYG Designer, you can layout your reports, set up your data source and let DynamicPDF ReportWriter do the rest.
Nevron Chart