This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a script to find all dates between two given dates in SQL Server.
Today, Morning one database developer came to my desk and asked about this query.
I created a small script for him, so here I am also sharing this script which will help other.
I am using WITH statement for finding a list of all dates between two given dates.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
DECLARE @StartDateTime DATETIME DECLARE @EndDateTime DATETIME SET @StartDateTime = '2015-01-01' SET @EndDateTime = '2015-01-12'; WITH DateRange(DateData) AS ( SELECT @StartDateTime as Date UNION ALL SELECT DATEADD(d,1,DateData) FROM DateRange WHERE DateData < @EndDateTime ) SELECT DateData FROM DateRange OPTION (MAXRECURSION 0) GO |
Result:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DateData ----------------------- 2015-01-01 00:00:00.000 2015-01-02 00:00:00.000 2015-01-03 00:00:00.000 2015-01-04 00:00:00.000 2015-01-05 00:00:00.000 2015-01-06 00:00:00.000 2015-01-07 00:00:00.000 2015-01-08 00:00:00.000 2015-01-09 00:00:00.000 2015-01-10 00:00:00.000 2015-01-11 00:00:00.000 2015-01-12 00:00:00.000 |
