It's been a while since last post. There has been a lot of life changes lately and now I'm back again, and will try to post more regulary. The promise is try, and hopefully I'll be more frequent again.
Todays' post is about one of my new favourites, generate_series(@start int, @stop int, @step int).
There are numerous needs to generate number series, and one of the needs is for creating date tables.
DECLARE
@start date = '1900-01-01',
@years int = 150;
DECLARE
@days int = DATEDIFF(day, @start, dateadd(year, @years, @start)) - 1;
SELECT dateadd(day, value, @start) FROM generate_series(0, @days, 1)
The first declare sets the starting date, and the number of years, the second declare gives you the number of days needed. The function generate_series has three parameters:
@start, which is the lowest number in the sequence
@stop, which is the highest number in the sequence
@step, which is the step size of the sequence
In the example we start at 0 and then step one up to the number of days between '1900-01-01' and '2049-12-31', which is 54787 days. The higest number in the sequence is 54786. This value is then added on the starting date.
