• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 758
  • Last Modified:

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
0
halifaxman
Asked:
halifaxman
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please define "working day" . does it include holidays?
0
 
halifaxmanAuthor 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
halifaxmanAuthor 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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now