Generate dates in a date range using U-SQL

user_8331858 0
Viewed 997

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

u-sql range
4 Answers

This is a prime example of where the .Net elements of the U-SQL language can be used to great effect. In this case you can explode an Enumerable.Range to get an incrementing list of values that you can apply to your data:

DECLARE @startDate DateTime = DateTime.Parse("2000/01/01");
DECLARE @endDate DateTime = DateTime.Parse("2017/12/31");

@dates =
    SELECT d.DateValue
    FROM (VALUES(@startDate)) AS sd(s)
         CROSS APPLY    // EXPLODE creates a rowset from all the values in the given list
             EXPLODE(Enumerable.Range(0
                                     ,(@endDate - @startDate).Days
                                     )
                                     .Select(offset => sd.s.AddDays(offset))
                    ) AS d(DateValue)
    ;
user_13509870
Related