Returning the Nth weekday in SQL…and in InfoPath

Returning the Nth weekday in SQL…and in InfoPath

Ok, after much research I found a forum that had a posting that demonstrated how to get the Nth chosen weekday of a month using T-SQL. Here’s the code:

DECLARE @date datetime
DECLARE @weekday int
DECLARE @day datetime
DECLARE @number int
DECLARE @WhichOne int

SELECT @WhichOne = 2 — Set this to the week you want to grab
SELECT @number = 1
SELECT @weekday = 0
SELECT @date = getDate() — Change to dateadd(month, 1, getDate()) for next month, etc.

WHILE @weekday 4 — Change this value to be the day of the week you’re looking for
SELECT @day = (CAST(STR(MONTH(@date)) + ‘/’ + STR(@number) + ‘/’+ STR(YEAR(@date)) AS DATETIME))
SELECT @weekday = DATEPART(weekday, @day)
SELECT @number = @number + 1

SELECT DATEADD(d, (@WhichOne – 1) * 7, @day)

Many MANY thanks to Tara for this posting! The link to the forum post is here.

Now, how did I get InfoPath to see this? In the InfoPath form, I added a DatePicker control. Next I had to create a Data Source, point to a server and database on our network somewhere (doesn’t matter where). Click “Edit SQL” and add this code to that box. Click through the wizard and right click on the control, choosing Properties. Click the Function design button next to Default Value, Click Insert Field or Group, and under Data Source, choose your newly created data source. Expand out the tree until you see your result set field and choose it. Don’t forget to export your data connection to a Data Connection library on your SharePoint server and site, or the form won’t work.

Comments are closed.
%d bloggers like this: