2nd Working Day of Month

please define "working day" . does it include holidays?

It doesnt need to include holidays, just need to include weekends.

i.e. For November 2nd working day would be 4th November

This code:
IF ( DATEPART(DAY, GETDATE()) = 1)
    BEGIN
        SET @start = etc
Is not necessarily returning the first working day of the month, but rather is checking if the DAY in question is a Monday.  That does not guarantee that that is the first working day of the month.
 

Hi

the code

IF ( DATEPART(DAY, GETDATE()) = 1)
    BEGIN
        SET @start = etc

I am not looking for the first working day of the month with the code above, I am looking or the first day of the month i.e. the 1st November for this month. Which worked fine when the query ran on the 1st November.

I am now looking for the 2nd working day instead

Hi halifaxman,

I am not familiar with SP, but below code should give you the idea, you can find any working day you like base on year month.

CREATE PROCEDURE SP_WORKING_DAY(YEAR STRING, MONTH STRING, WORKINGDAY INTEGER)
STARTDATE = YEAR & '/' & MONTH & "/01"
ENDDATE =YEAR & '/' & MONTH & "/10"

WHILE (STARTDATE < ENDDATE)
 
BEGIN
IF datepart(weekday, STARTDATE) = WORKINGDAY   BEGIN
RETURN STARTDATE
END
STARTDATE =  STARTDATE + 1
END

Hope it could help.

Post in ready Stored Procedure.


Thank you.

In VB Code :

 Public Shared Function Get_Working_Date(ByVal strYear As String, _
                                            ByVal strMonth As String, _
                                            ByVal intWorkingDay As Integer) As Date

        Dim dteStartDate As Date = strYear & "/" & Format(CInt(strMonth), "00") & "/01"
        Dim dteEndDate As Date = strYear & "/" & Format(CInt(strMonth), "00") & "/10"

        While dteStartDate <= dteEndDate
            dteStartDate = DateAdd(DateInterval.Day, 1, dteStartDate)

            If DatePart(DateInterval.Weekday, dteStartDate) = intWorkingDay Then
                Return dteStartDate
            End If

        End While

        Return "1900/01/01"

    End Function

Thank you.