Generate dates in a date range using U-SQL

I need to populate a rowset with all the dates between a defined Start date and End Date. If my start date is 19/7/2017 and the end date is 21/7/2017 then the rowset should contain 19/7/2017, 20/7/2017 and 21/7/2017.

I was wondering if there was an easy way to do this using U-SQL

Solution

We always recommend that developers investigate using the pure U-SQL approach first instead of using C# UDOs, here is another way of accomplishing this task.

First, consider how you would just get a list of numbers in U-SQL

@numbers_10 = 
    SELECT
        *
    FROM 
    (VALUES
        (0),
        (1),
        (2),
        (3),
        (4),
        (5),
        (6),
        (7),
        (8),
        (9)
    ) AS T(Value);

That's just 10 numbers - 0 to 9. We can use CROSS JOIN to expand the list.

@numbers_100 = 
    SELECT (a.Value*10 + b.Value) AS Value
    FROM @numbers_10 AS a 
        CROSS JOIN @numbers_10 AS b;

Now we have 0 to 99. We can use CROSS JOIN to generate even more numbers.

@numbers_10000 = 
    SELECT (a.Value*100 + b.Value) AS Value
    FROM @numbers_100 AS a CROSS JOIN @numbers_100 AS b;

Then generate a list of dates from that.

DECLARE @StartDate = DateTime.Parse("1979-03-31");

...

@result = 
    SELECT 
        Value,
        @StartDate.AddDays( Value ) AS Date
    FROM @numbers_10000;

The full script looks like this:

DECLARE @StartDate = DateTime.Parse("1979-03-31");

@numbers_10 = 
    SELECT
        *
    FROM 
    (VALUES
        (0),
        (1),
        (2),
        (3),
        (4),
        (5),
        (6),
        (7),
        (8),
        (9)
    ) AS T(Value);

@numbers_100 = 
    SELECT (a.Value*10 + b.Value) AS Value
    FROM @numbers_10 AS a CROSS JOIN @numbers_10 AS b;

@numbers_10000 = 
    SELECT (a.Value*100 + b.Value) AS Value
    FROM @numbers_100 AS a CROSS JOIN @numbers_100 AS b;

@result = 
    SELECT 
        Value,
        @StartDate.AddDays( Value ) AS Date
    FROM @numbers_10000;

OUTPUT @result TO "/res.csv" USING Outputters.Csv(outputHeader:true);

Once you have your list of numbers or dates, it might be convenient to save it into a U-SQL table so you can retrieve the list easily later.