SQL UDF to return an array of dates

Question

I have currently written a small UDF (Table Value Type) to return a table of dates between a start and end date. Start and End dates are already retrieved from another table.

Start_End_Table
WeekNumber | Start Date | End Date

Function:

CREATE FUNCTION [dbo].[DatesBetween](@startDate datetime, @endDate datetime)
RETURNS @dates TABLE (
   DateValue datetime NOT NULL
) 
AS
BEGIN
   WHILE (@startDate <= @endDate) BEGIN
      INSERT INTO @dates VALUES (@startDate);
      SET @startDate = DATEADD(day, 1, @startDate);
   END;

   RETURN;
END;

Until now, I have been using this function in the following manner,

SELECT * FROM [dbo].[DatesBetween](@startDate datetime, @endDate datetime);

Going forward I need to get start, end dates from Start_End_Date table and call the function. Thus I do not think returning a table is an option any longer. How can I get this function to return an array of dates instead of a table? Or is there any other way I could wrap this up?

a more efficient approach would be to set up a permanent pre-populated date table (or a calendar table as they are sometimes called), index it, and filter on that table instead of using a WHILE loop. — g2server
– g2server, Commented Jul 23, 2014 at 3:14
I'm a little confused. Are you calling the function using variables currently? — shree.pat18
– shree.pat18, Commented Jul 23, 2014 at 3:17
@g2server setting up a calendar table isn't an option as the tables are out of our scope for change...currently we are given start, end dates and week number columns. So I have to use those two to get the set of dates. — bonCodigo
– bonCodigo, Commented Jul 23, 2014 at 3:20
@shree.pat18 currently using variables for testing purpose - but I must use the columns available in the start_end_table within the function. Is there a way star, end dates can be incorporated into one query while using the function or what changes do I have to do to the function? — bonCodigo
– bonCodigo, Commented Jul 23, 2014 at 3:36

Solomon Rutzky · Accepted Answer · 2014-07-23 05:21:58Z

2

Try CROSS APPLY as it will run the function for each row in the JOINed table:

SELECT [Date Value]
FROM dbo.Start_End_Table dates
CROSS APPLY [dbo].[DatesBetween](dates.StartDate, dates.EndDate)
WHERE [Week Number]=@WeekNumber;

A sample output would be something like for a start date = 1/1/2014, end date = 1/3/2014, @weeknumber = 1:

 | DateValue
  1/1/2014
  1/2/2014
  1/3/2014

SQL Fiddle

EDIT:
Try the following Inline Table-Valued Function as it will be much faster than your multi-line TVF due to:

Inline TVFs are inherently faster than multi-line TVFs
It is set-based rather than relying on a WHILE loop

--

CREATE FUNCTION dbo.GetDates(@StartDate DATETIME, @EndDate DATETIME)
RETURNS TABLE
AS RETURN
WITH cte AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS [RowNum]
    FROM sys.objects WITH (NOLOCK)
)
SELECT DATEADD(DAY, (cte.[RowNum] - 1), @StartDate) AS [DateValue]
FROM cte
WHERE cte.[RowNum] < (DATEDIFF(DAY, @StartDate, @EndDate) + 2);
GO

SELECT * FROM dbo.GetDates('1/1/2014', '1/3/2014');

edited Jul 23, 2014 at 5:21

answered Jul 23, 2014 at 4:55

Solomon Rutzky

49.3k11 gold badges141 silver badges185 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

bonCodigo Over a year ago

The number of dates generated are always 7. Start Date and End Date refers to a week start and end date. So we are retrieving week dates FYI. A year has 52 week and user has the ability to go back and forth between last year and current year

Nizam Over a year ago

In this case, there is an assumption that sys.objects will have a sufficient number of objects to cover the interval, right?

Solomon Rutzky Over a year ago

@Nizam: yes, I assumed it would never go above 7 so it was safe. If more dates are needed then another table such as sys.all_columns could be used. That or a CROSS JOIN to sys.objects again.

Solomon Rutzky Over a year ago

@bonCodigo: no, my edit did not change anything. It is simply a swap-out replacement for your DatesBetween function. That is all. It still fits into the CROSS APPLY query that is the first part of my answer. I called it GetDates but you can rename my proposed function to DatesBetween if that makes things clearer.

Nizam Over a year ago

@bonCodigo everything continues the same. He only changed your UDF. The you will use the new function and the CROSS APPLY he explained before. The query will be the same changing the UDF DatesBetween to GetDates which seems much faster.

|

Collectives™ on Stack Overflow

SQL UDF to return an array of dates

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related