[Solved] Get the Previous Sunday from a Date

[AndyGable]

Hi All

I need some help

I am trying to get the Previous sunday from a date (for example 23/03/2024 should return me the sunday date of 17th March)

as I need to get the Sunday to Monday dates for a report that run for the previous week (no matter what day the report would be run this week it would always report back the previous Monday to Sunday dates)

This is what I have so for

Private Function GetPreviousWeekDayDate(inputDate As Date) As Date
    ' Calculate the number of days to subtract to find the previous week day
    Dim daysToSubtract As Integer
    daysToSubtract = WeekDay(inputDate + 5) Mod 7

    ' Subtract the calculated days from the input date
    Dim previousWeekDayDate As Date
    previousWeekDayDate = inputDate - daysToSubtract

    Return previousWeekDayDate
End Function

Private Function GetSundayDate(inputDate As Date) As Date
    ' Calculate the number of days to subtract to find the Sunday date
    Dim daysToSubtract As Integer
    daysToSubtract = WeekDay(inputDate) - 1 ' Sunday is 1, Saturday is 7

    ' Subtract the calculated days from the input date
    Dim sundayDate As Date
    sundayDate = inputDate - daysToSubtract

    Return sundayDate
End Function

This is how i am calling them at the moment

       dtStartDate = GetPreviousWeekDayDate(Now)
          dtEndDate = GetSundayDate(dtStartDate)

    Dim StartOfWeekDay As String = "Monday " & Format(dtStartDate, "dd/mm/yyyy")
      Dim EndOfWeekDay As String = "Sunday " & Format(dtEndDate, "dd/mm/yyyy")

But I am getting at the moment getting Start Date : Monday 19/03/2024 and End Date : Sunday 18/03/2024

if anyone has any better ideas as how to get previous weeks dates I would be most grateful as I have been on this report Since Saturday afternoon

[BruceSteers]

You cannot use a Date object like that by just adding/subtracting integers from it.

You must use DateAdd()
https://gambaswiki.org/wiki/lang/dateadd

DateAdd() minus 1 week

EDIT: sorry i had the gb.Week/gb.Day and the values round the wrong way.

Private Function GetPreviousWeekDayDate(inputDate As Date) As Date

    Return DateAdd(inputDate, gb.Week, -1)

End

And DateAdd again minus the days

Private Function GetSundayDate(inputDate As Date) As Date
    ' Calculate the number of days to subtract to find the Sunday date
    Dim daysToSubtract As Integer
    daysToSubtract = WeekDay(inputDate) - 1 ' Sunday is 1, Saturday is 7
 
    ' Subtract the calculated days from the input date
    Dim sundayDate As Date
    sundayDate = DateAdd(inputDate, gb.Day, -daysToSubtract)
 
    Return sundayDate

End

[AndyGable]

You cannot use a Date object like that by just adding/subtracting integers from it.

You must use DateAdd()
https://gambaswiki.org/wiki/lang/dateadd

DateAdd() minus 1 week

Private Function GetPreviousWeekDayDate(inputDate As Date) As Date

    Return DateAdd(inputDate, -1, gb.Week)

End

And DateAdd again minus the days

Private Function GetSundayDate(inputDate As Date) As Date
    ' Calculate the number of days to subtract to find the Sunday date
    Dim daysToSubtract As Integer
    daysToSubtract = WeekDay(inputDate) - 1 ' Sunday is 1, Saturday is 7
 
    ' Subtract the calculated days from the input date
    Dim sundayDate As Date
    sundayDate = DateAdd(inputDate, -daysToSubtract, gb.Day)
 
    Return sundayDate

End

Thank-you @BruceSteers

How would I change the Sunday Function to get the last Day of the Month for a given month? example say I want to get the last day of March (31st) is this possible with Gambas?

[thatbruce]

The last day of the month is the 1st day of the next month minus one day.

[AndyGable]

The last day of the month is the 1st day of the next month minus one day.

yea I know but does that take into account leaps years?

in VB.net I can do something like Date = Month.firstday and it will retun

01/01 or 01/02 etc and then I could do Month.lastday and it would return for March 31.03

I need to beable to get this working as they are used for Reports in my system (I am not sure yet how I will hand week that bridge two months) example of this would be 31st Jan (Wednesday) and the 1st Feb (Thursday)

I am at the moment assuming the sql between statement would work (I have no data yet for the time frame to test it on)

[BruceSteers]

So i spent a whole minute writing this. (thanks to the clues given by the other Bruce )

Public Sub Button1_Click()

  Debug GetLastMonthDay(Now)
  Debug Format(GetLastMonthDay(Now), "dddd dd mmm")

End



Public Sub GetLastMonthDay(hDate As Date) As Date

  Dim d As Date

  d = DateAdd(hDate, gb.Day, 1 - Day(hDate))  ' set day to first of the month
  d = DateAdd(d, gb.Month, 1)  ' advance 1 month (will be first of next month leap year or not)
  d = DateAdd(d, gb.Day, -1)  ' subtract one day

  Return d

End

Form1.Button1_Click.8: 31/03/2024 01:22:35
Form1.Button1_Click.9: Sunday 31 Mar

PS. I edited the original example as i got the gb.Month/gb.Day and the values the wrong way round. i wrote d = DateAdd(d, -1, gb.Month) where i should have written d = DateAdd(d, gb.Month, -1)

[AndyGable]

So i spent a whole minute writing this. (thanks to the clues given by the other Bruce )

Public Sub Button1_Click()

  Debug GetLastMonthDay(Now)
  Debug Format(GetLastMonthDay(Now), "dddd dd mmm")

End



Public Sub GetLastMonthDay(hDate As Date) As Date

  Dim d As Date

  d = DateAdd(hDate, gb.Day, 1 - Day(hDate))  ' set day to first of the month
  d = DateAdd(d, gb.Month, 1)  ' advance 1 month (will be first of next month leap year or not)
  d = DateAdd(d, gb.Day, -1)  ' subtract one day

  Return d

End

Form1.Button1_Click.8: 31/03/2024 01:22:35
Form1.Button1_Click.9: Sunday 31 Mar

PS. I edited the original example as i got the gb.Month/gb.Day and the values the wrong way round. i wrote d = DateAdd(d, -1, gb.Month) where i should have written d = DateAdd(d, gb.Month, -1)

That works great Thanks @BruceSteers

I think I may have broke my Sub that I have been using to get the first Day of the month

Private Function GetfirstDayOfMonth(MonthName As Integer) As String
    Dim D As String
    Dim Year As String = Year(Now)

    If MonthName = 12 Then Year -= 1
    
    D = "01/" & Format(MonthName, "00") & "/" & Year

    Return D
 End

It returns for Feb 02/01/2024 (i know that is correct but I need to have it formatted as dd/mm/yyyy)

[BruceSteers]

That cannot be true

you make D a string object not a Date object.
Then you make the string and return it...

D = "01/" & Format(MonthName, "00") & "/" & Year

Return D

D will begin with 01/ . just like you made it
Unless somewhere else along the line you are using a Date or Format function on the String object.
You should not mix Date and String , they are not the same even though they look it they are treated differently by the interpreter.

Look at this...

Dim DD As Date = Now
Dim DS As String = Now

Print "DD="; DD
Print "DS="; DS

Prints this...
DD=27/03/2024 02:24:00
DS=03/27/2024 02:24:00.203

The date as a Date object is localized the way you want it, the string is not.

You also make Year a String object then use Integer operations on it!

I think I've given all the advice I can on this. All the answers are in the previous code.

[AndyGable]

That cannot be true

you make D a string object not a Date object.
Then you make the string and return it...

D = "01/" & Format(MonthName, "00") & "/" & Year

Return D

D will begin with 01/ . just like you made it
Unless somewhere else along the line you are using a Date or Format function on the String object.
You should not mix Date and String , they are not the same even though they look it they are treated differently by the interpreter.

You also make Year a String object then use Integer operations on it!

I think I've given all the advice I can on this. All the answers are in the previous code.

I have HARD coded dtStartDate to be "01/02/2024" but when you hover over dtStartDate it show as "02/01/2024" at least we know why dtEndDate is showing as January 31st lol

Both dtStartDate and dtEndDate are declared as Date So am I doing something else wrong or could be local settings of my PC be overriding the hard coding and making it show up as a "02/01/2024"??

[BruceSteers]

I'm not sure what you mean by Hover over them?
Gambas will just display the date object as it is.

If they are using Date objects not Strings then just use Format where you need it formatted

sDisplayString = Format(dtStartDate,"dddd dd/mm/yyyy")