Get Monday from Date column (Brainfog)

[rsj88]

Hi,

I had this before but i cant rem how i did it

Need a sql query to identify the week commencing date from the date column. any ideas?

Thanks

 

[jackd]

See this post for an example that should offer you some insight.

 

[jackd]

Is this what you are looking for?

' ----------------------------------------------------------------
' Procedure Name: getWeekStartMonday
' Purpose: Show the Date of the Monday of the week  where the parameter date exists
' Procedure Kind: Function
' Procedure Access: Public
' Parameter InDate (Date): A weekday
' Return Type: Date
' Author: Jack
' Date: 11-Jul-22
' Usage: 14-Jul-22 is a Thursday
'?getWeekStartMonday(#7-14-22#) 'to get Monday Date in the week containing 14-Jul-22
'11-Jul-22'Is the Monday
' ----------------------------------------------------------------
Function getWeekStartMonday(InDate As Date) As Date
    Dim wkDay As Date
    Select Case Weekday(InDate)
        Case 1 'Sun
            getWeekStartMonday = InDate + 1
        Case 2 'Mon
            getWeekStartMonday = InDate + 0
        Case 3 'Tue
            getWeekStartMonday = InDate - 1
        Case 4 'Wed
            getWeekStartMonday = InDate - 2
        Case 5 'Thu
            getWeekStartMonday = InDate - 3
        Case 6 'Fri
            getWeekStartMonday = InDate - 4
        Case 7 'Sat
            getWeekStartMonday = InDate - 5
    End Select
End Function

 

[rsj88]

Thanks mate.

ended up using this:

WeekCom: [ReceiptDate]+1-Weekday([ReceiptDate]+6)

Which also worked