Function to find the Nth Xday of each month between two dates 2

[ClaytonQ]

I'm trying to write a T-SQL function that can find the third Tuesday, or the fourth Saturday, or the second Wednesday of every month. I found a VBScript version of something similar at

http://www.merlyn.demon.co.uk/vb-dates.htm#ILS.

VBScript and T-SQL don't do dates exactly the same way. I'm having a hard time wrapping my brain around how this is supposed to work. Here's my (non-working) function:

CREATE FUNCTION ufn_NthXday(@StartDate smalldatetime, @EndDate smalldatetime, @Nth tinyint, @XDay tinyint)
RETURNS @DayList TABLE (EventDate smalldatetime primary key)

AS
BEGIN
--Set up a @TempDate. Initialize it at @StartDate
DECLARE @TempDate smalldatetime
DECLARE @TempMonth tinyint
DECLARE @ZeroethDayOfMonth smalldatetime
SET @TempDate = @StartDate
WHILE @TempDate <= @EndDate
	BEGIN
	--Find out what month @TempDate is in and call it @TempMonth
	SET @TempMonth = Datepart (month,@TempDate)
	SET @ZeroethDayOfMonth = @TempDate - Datepart(day,@TempDate)
	--Find the Nth Xday of @TempMonth. Make that the new @TempDate.
	--From [URL unfurl="true"]http://www.merlyn.demon.co.uk/vb-dates.htm#ILS[/URL]
	SET @TempDate = @ZeroethDayOfMonth + (7 * @Nth) - ((@ZeroethDayOfMonth - @Xday) % 7)
	--Is @TempDate between @StartDate and @EndDate? 
	If @TempDate BETWEEN @StartDate AND @EndDate
	--If so, keep it. Write it to @DayList.
		BEGIN
		INSERT INTO @DayList (EventDate) 
		VALUES (@TempDate)
		END
	--Go to the next month (add a month to @TempDate) 
	SET @TempDate = DATEADD ( month , 1, @TempDate )
	END

RETURN
END
GO

This code returns an error that says you can't use the modulo operator on datatype smalldatetime. You can in VBScript. To work around it, my first thought was to find out what day the Zeroeth Day of the Month (the last day of the previous month) was on using Datepart(day,@ZeroethDayOfMonth). This got me past the syntax error, but did not return the correct answers. The root of my problem is that I don't really grok the code I lifted from merlyn to begin with. Any insight would be greatly appreciated.

THANKS!

 

[rac2]

Try writing the expressions involving SMALLDATETIME values with the function, DATEADD(datepart_code, integer, datetime_value), instead of as arithmetic expressions. DATEADD() returns a DATETIME value. And DATEDIFF(datepart_code, datetime_value_start, datetime_value_end) returns an integer value for the number of dateparts between the two dates.

SET @ZeroethDayOfMonth = 
  DATEADD(day,  - Datepart(day,@TempDate, @TempDate)

@ZeroethDayOfMonth is an odd idea. There is no December 0, 2004, at least there wasn't last I heard.


But this next piece is a real mind-twister. What could this mean, ((@ZeroethDayOfMonth - @Xday) % 7) ?

If @ZeroethDayOfMonth is a DATETIME value as you have declared then it seems that (@ZeroethDayOfMonth - @Xday) would be a date a few days earlier. And as the error message says, the modulo operator is meaningless for dates.

If we really need to use the modulo operator, then (@ZeroethDayOfMonth - @Xday) must be an integer, and @ZeroethDayOfMonth must be an integer, not a date. So I come back to wondering what the zero day of the month means.

Regarding your assertion, "You can in VBScript". VBScript may not object when you do this

((@ZeroethDayOfMonth - @Xday) % 7)

But I would say that is because, VBScript casts the date to an integer, then does the arithmetic. The modulo operator must have integers as arguments, no matter what the programming language.



I imagine the function should return a DATETIME value, the date of the 2nd Tuesday for example would be December 9, 2004. Omigosh that is today!

 

[ClaytonQ]

You are absolutely correct about VBScript casting the date to an integer before it does the modulus. And Zeroeth day of the month is a bizarre idea.

I can see how taking the last day of the previous month and adding 7 times the Nth (1 for the first whatever day, 2 for the second, etc.) gets you to the upper bound of the right answer. The right answer would be that number minus SOMETHING between 0 and 6. Take any number and Modulo 7, and the answer will be between 0 and 6. In the vbscript example that that Merlyn guy did, the number he used to Modulo by 7 was the vbscript integer equivalent to the last day of the previous month minus an integer representing what day you are concerned with, where Sun=1, Mon=2, etc. I don't understand how vbscript translates a date into an integer, and I don't understand how he came up with this formula, so I can't translate it into T-SQL.

I'm willing to scrap the whole formula if there's a better way to calculate the Nth Xday of a given month. It seems like the kind of thing that everybody would need to know how to do. I'm surprise the functionality isn't built in to SQL (as far as I can tell).

To translate all the arithmetic into DateAdd and DateDiff, I came up with this:

SET @TempDate = DateAdd(day,(7 * @Nth) - (DateDiff(day,DateAdd(day,-@Xday,@ZeroethDayOfMonth),@ZeroethDayOfMonth) % 7),@ZeroethDayOfMonth)

I asked it to show me the third Tuesday of every month from 12/1/2004 to 6/30/2005. The answer it gave was the 18th of every month, which is obviously wrong. This stuff makes me cross-eyed.

Thank you very much for your interest and help, rac2.

 

[vongrunt]

I checked this in banzai mode only but try it anyway:

ALTER FUNCTION ufn_NthXday(@StartDate smalldatetime, @EndDate smalldatetime, @Nth tinyint, @XDay tinyint)
RETURNS @DayList TABLE (EventDate smalldatetime)
AS
BEGIN

DECLARE @TempDate smalldatetime; set @TempDate = dateadd(mm, datediff(mm, 0, @startdate), 0)

while @TempDate <= @EndDate
begin
	insert into @DayList 
	select EventDate from 
		( select @TempDate + (7*@Nth - (datepart(dw, @TempDate)-@XDay)%7) as EventDate ) X
		where EventDate between @startDate and @endDate
		and datediff(mm, 0, @TempDate) = datediff(mm, 0, EventDate)

	set @TempDate = dateadd(mm, 1, @TempDate)
end

RETURN
END
go

-- select * from dbo.ufn_NthXday( '12/1/2004', '6/30/2005', 3, 3)

 

[ClaytonQ]

This link is closer to the correct part of the long page I got the original formula from:

http://www.merlyn.demon.co.uk/vb-dates.htm#NXFM

You have to view source to see the vbscript he wrote. The page exists to teach people how to do this stuff, so I'm certain he wants people to use his ideas.

 

[ClaytonQ]

vongrunt, this is a huge leap forward. It's right most of the time. When you run it looking for the third Tuesday like this, it returns a correct answer UNLESS there is a Tuesday in the first week of the month. When that happens, this code returns the fourth Tuesday. This is really close. Thanks.

 

[vongrunt]

I supposed something like that... modulo arithmetic can be tricky. Attempt #2: replace line between SELECT and WHERE with:

       ( select @TempDate + 7*(@Nth-1) + (7-datepart(dw, @TempDate)+@XDay)%7  as EventDate ) X

 

[ClaytonQ]

Works every time, vongrunt. This is awesome. *skipping unashamedly around desk*