TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
C# Corner
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
How to get all dates between date range in sql query
Nipun Tomar
Feb 25, 2012
56
k
0
0
facebook
twitter
linkedIn
Reddit
WhatsApp
Email
Bookmark
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
Next Recommended Reading
Get Week Start Date & Week End Date Using SQL Server