2nd Working Day of Month

The query I am using, looks for the first day of the month as follows


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

Instead of looking at the 1st day of the month, I now want to look at the 2nd working day of the month

i.e. IF ( DATEPART(day, GETDATE()) = 2nd working of month)
    BEGIN
        SET @start = etc

Is this possible?

Thanks
Mark WilsonBI DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
please define "working day" . does it include holidays?
0
Mark WilsonBI DeveloperAuthor Commented:
It doesnt need to include holidays, just need to include weekends.

i.e. For November 2nd working day would be 4th November
0
Arthur_WoodCommented:
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.
 
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Mark WilsonBI DeveloperAuthor Commented:
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

0
emi_sastraCommented:
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.
0
emi_sastraCommented:
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.
0
emi_sastraCommented:
Try this one.

Let me know if it is working or not. I have not tested it yet.

Thank you.


   Public Shared Function Get_Working_Date(ByVal strYear As String, _
                                            ByVal strMonth As String, _
                                            ByVal strHolidays As String, _
                                            ByVal intTheNWorkingDay As Integer) As Date
 
        Dim intWorkingDayCount As Integer = 0
        Dim blnHoliday As Boolean = False
 
        Dim dteStartDate As Date = strYear & "/" & Format(CInt(strMonth), "00") & "/01"
        Dim dteEndDate As Date = strYear & "/" & Format(CInt(strMonth), "00") & "/10"
 
        Dim strSplitHolidays() As String = Split(strHolidays, ",")
 
        While dteStartDate <= dteEndDate
 
            If DatePart(DateInterval.Weekday, dteStartDate) <> FirstDayOfWeek.Saturday And _
               DatePart(DateInterval.Weekday, dteStartDate) <> FirstDayOfWeek.Sunday Then
 
                blnHoliday = False
 
                For intCounter As Integer = 0 To strSplitHolidays.Length - 1
                    If Format(dteStartDate, "yyyy/MM/dd") = _
                       Format(CDate(strSplitHolidays(intCounter)), "yyyy/MM/dd") Then
                        blnHoliday = True
                        Exit For
                    End If
                Next
 
                If Not blnHoliday Then
                    intWorkingDayCount += 1
 
                    If intWorkingDayCount = intTheNWorkingDay Then
                        Return dteStartDate
                    End If
                End If
            End If
 
            dteStartDate = DateAdd(DateInterval.Day, 1, dteStartDate)
 
        End While
 
        Return "1900/01/01"
 
    End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.