Systems Engineering and RDBMS

Generating Dates between two date values

Posted by decipherinfosys on February 4, 2008

Last week, there was a requirement that was posed to us by one of our clients which needed to make use of the pipeline function. This client has a product that runs on both Oracle and SQL Server so a solution was needed for both the RDBMS. The requirement was that given two dates, the code should automatically generate all the dates that fall between those dates including the end date. If there are no days that fall between those 2 dates, only the end date should be displayed. This output was to be used in other SQLs so either this should be procedural code or if possible, it should be a function call that can then be used within other SQL statements.

We had blogged before on how to simulate pipeline functions in SQL Server. It can be done by either maintaining a table of numbers which is a very popular practice or can also be done by using a UDF in which we use a table variable to populate the results. This second technique is what we had demonstrated in that blog post. Another way of doing this is via a CTE using recursion. In this post, we will present the solution that we used for this client using a UDF – we went with this solution after noticing the performance benefits of this approach versus the others.  Let’s look at the UDF again:

CREATE function dbo.UDF_GEN_SEQUENCE
(
@Nbr_Of_Rows int,
@Start_Nbr int
)
returns @table table
(
Row_Num int not null primary key,
Data_Value int
)
as
begin
/******************************************
Seed the table with the initial value
******************************************/
insert @table values (1, @Start_Nbr)

/******************************************
Now, loop through and create the list
till the rowcount of the inserts is
more than 0
*******************************************/
while @@ROWCOUNT > 0
begin
insert into @table (Row_Num, Data_Value)
select t1.Row_Num + t2.Max_Row_Num, @Start_Nbr + t1.Row_Num + t2.Max_Row_Num – 1
from @table as t1
cross join
(select max (Row_Num) Max_Row_Num from @table) t2 /*derived table*/
where t1.Row_Num <= @Nbr_Of_Rows – t2.Max_Row_Num
end
return
end
GO

Using this UDF, as we had demonstrated in that older post, one can generate any sequence of numbers after one passes in the number of records and the starting number from where we need the values from, example:

select * from dbo.UDF_GEN_SEQUENCE (9, 10);

Row_Num     Data_Value
———– ———–
1           10
2           11
3           12
4           13
5           14
6           15
7           16
8           17
9           18

In this example, we wanted 9 records with the data value starting from number 10.  So, now that we have this pipeline function, we can make use of it to generate the dates as well, example:

declare @start_date datetime, @end_date datetime, @i int
select  @start_date = ’01/01/2008′,
@end_date = ’01/10/2008′

select @i = datediff(dd, @start_date, @end_date)

select @start_date + data_value as date_progression
from dbo.UDF_GEN_SEQUENCE (@i, 1)

date_progression
———————–
2008-01-02 00:00:00.000
2008-01-03 00:00:00.000
2008-01-04 00:00:00.000
2008-01-05 00:00:00.000
2008-01-06 00:00:00.000
2008-01-07 00:00:00.000
2008-01-08 00:00:00.000
2008-01-09 00:00:00.000
2008-01-10 00:00:00.000

Using this technique, we can then use this result set as part of other SQL statements as well.

One Response to “Generating Dates between two date values”

  1. […] Mondays in a year.  He was referencing one of our earlier posts in which we had discussed how to generate dates between two date values and wanted to know if we can extend it to provide him an answer to this current question as well. […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: