Please enjoy and feel free to adjust to your specific needs!
If you find a way to optimize the function further, just reply with your code fix.


CREATE FUNCTION dbo.fnSeqDates
(
@LowLimit DATETIME,
@HighLimit DATETIME
)
RETURNS @Values TABLE
(
Value DATETIME
)
AS

BEGIN
DECLARE @Temp DATETIME

IF @LowLimit > @HighLimit
SELECT @Temp = @LowLimit,
@LowLimit = @HighLimit,
@HighLimit = @Temp

INSERT @Values VALUES (@LowLimit)

WHILE @@ROWCOUNT > 0
INSERT @Values
SELECT DATEADD(dd, t.Items, d.Value)
FROM @Values d
CROSS JOIN (
SELECT COUNT(*) Items
FROM @Values
) t
WHERE DATEADD(dd, t.Items, d.Value) <= @HighLimit

RETURN
END

Hi Peso,

This function looks pretty cool but what does it do and how do you use it, execute it?

I ran the CREATE FUNCTION in QA and tried to run it but I only get the errors.

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '2/2/2005'.

Thanks,
Richard

It is a function that returns a result set of dates.

It is most often used when searching for missing dates and this function comes in handy for getting all dates so one know what to look for.

select * from dbo.fnSeqDates('2/2/2005','2/2/3006')

This should return all dates between february 2, 2005 and february 2, 3006. That should be around 365 600 dates.

why are you doing a count?

Because I want to select all already stored numbers, increment them and store them again.

First there is 1 value (@LowLimit). Then I select this value, add it with the number of already stored values (1), add (@LowLimit + 1), and save.

Next iteration, I select the two already stored values (@LowLimit and @LowLimit + 1), increment them with the numbers already stored (2) and save the new values (@LowLimit + 2 and @LowLimit + 3).
And so on, so on... Until the @LowLimit reaches @HighLimit.

First insert, 1 record.
Second insert, 2 records.
Third insert, 4 records.
Fourth insert, 8 records.
Fifth insert, 16 records.
Sixth insert, 32 records.
Seventh insert, 64 records.
Eight insert, 128 records.
Ninth insert, 256 records.
Tenth insert, 512 records.
Eleventh insert, 1024 records.
Twelvth insert, 2048 records.

Until I get all the dates I want.

Also, you can use my function the answer the question
"How many workdays is there in August 2006?"

Ok, write

SELECT COUNT(*) FROM dbo.fnSeqDates('8/1/2006', '8/31/2006') DS WHERE DATEPART(dw, DS.Value) BETWEEN 2 AND 6

Or answer the question
"Which is the last friday in April, 2007"

Ok, write

SELECT MAX(DS.Value) FROM dbo.fnSeqDates('4/1/2007', '4/30/2007') DS WHERE DATEPART(dw, DS.Value) = 5

Peso,

Thank you so much for your date sequence generator. I was faced with a problem which was to select all of the Friday's for each quarter year. I was surprised and amazed when I got the exact results I was looking for with this query.

SELECT DS.Value FROM dbo.fnSeqDates('1/1/2006', '3/31/2006' ) DS WHERE DATEPART(dw, DS.Value) = 6 and DS.Value <= '3/31/2006'

Very much appreciated you sharing this function here.

Thanks,
Richard

I am satisfied you enjoyed the query!

Actually, you really don't need the last WHERE since the dates already are in the right range from the function.

Just write

SELECT Value Fridays
FROM dbo.fnSeqDates('1/1/2006', '3/31/2006')
WHERE DATEPART(dw, Value) = 6

OK. Yeah.. that makes sense.. I had put that last part of the WHERE statement in when I was messing around with it at first.


Very cool.

Thanks,
Richard