Get table of dates between two dates (period of dates) dynamically

Posted by on Jul 31, 2011 in Development, English | 4 comments

Well I am kind of sure anyone dealing with database queries must have come across this problem

The problem

You need a list (or better said) table of Dates between two dates (start date, end date ) that you have

 

The solution

Well usually you will be handling this using loops, coursers to do your operation row by row for each date between start date and end date,

While playing around I just found another way(I feel it is better way)

The Better way

Just found out that TSQL (since SQL SERVER 2005) new keyword is introduced WITH and it is used to created temporary table !

So why don’t we create a temp table using WITH that has dates between my start date and end date

Here is how

declare @start datetime

,@end datetime

 

set @start=’6/20/2011′;

set @end=’7/19/2011′;

 

with alldates as


(


select @start DateVal


union
all


select DateVal + 1


from alldates


where DateVal + 1 <= @end


)

 


select DateVal


from alldates;

 

This will list down dates

Well doing that will gave me all dates within my start date and end date

 

BUT

but still there is some problems with the WITH keyword (it could be better in SQL SERVER 2008) in SQL SERVER 2005 you can’t use it in complicated query

 

CORRECTION

 

Simple enough what I did currently is I can get the start date and the end date from another table by sql query , so I created a view that that uses the mighty WITH and creates dynamic list of dates between two dates in different table (note: this code is written as if the T_Period table has only one row, in case there were more rows a where condition could have been added like where PeriodToUse=1
)

create
VIEW [dbo].[V_DATES_OF_PERIOD]

AS

 

WITH alldates AS
(SELECT period_start AS DateVal


FROM dbo.T_Period

 


UNION
ALL


SELECT alldates_2.DateVal + 1 AS Expr1


FROM alldates AS alldates_2


WHERE
(alldates_2.DateVal+ 1 <=
(select period_end from dbo.T_Period)

))


SELECT DateVal


FROM alldates AS alldates_1

GO

 

SET
ANSI_NULLS
OFF

GO

SET
QUOTED_IDENTIFIER
OFF

GO

No related posts.

4 comments

  1. hassan
    August 1, 2011

    Hi, thanks Ali.

    But it seams that your solution is hard to understand with your CTE.

    Anyway if i understand the problem, this is another solution

    DECLARE @dateFrom AS DATETIME = ’2010-01-01′, @dateTo AS DATETIME=’2010-02-01′

    CREATE TABLE #tmpDate(DateVal VARCHAR(10));

    WHILE @dateFrom < @dateTo
    BEGIN
    INSERT INTO #tmpDate VALUES (CONVERT(VARCHAR(10), @dateFrom , 121))
    SET @dateFrom = DATEADD(DAY, 1, @dateFrom)
    END
    SELECT * FROM #tmpDate
    DROP TABLE #tmpDate

  2. Ali AL Lawati
    August 1, 2011

    welcome ,
    Hassan

    if i knew you will be commenting here i would have writtern the code in much better way :)

    yes your code will do it the right way, i was exited about the new way i found to do it :)

    i know the #tempTables caused me some problems with reporting services before, i am not sure if the WITH (the way in the post) will work ok in the reporting services or not :)

Leave a Reply