Page 1 of 3
[Solved] Get the Previous Sunday from a Date
Posted: Monday 25th March 2024 1:51am
by 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
Re: Get the Previous Sunday from a Date
Posted: Monday 25th March 2024 2:52am
by 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
Re: Get the Previous Sunday from a Date
Posted: Monday 25th March 2024 7:32pm
by AndyGable
BruceSteers wrote: ↑Monday 25th March 2024 2:52am
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?
Re: Get the Previous Sunday from a Date
Posted: Monday 25th March 2024 9:56pm
by thatbruce
The last day of the month is the 1st day of the next month minus one day.
Re: Get the Previous Sunday from a Date
Posted: Monday 25th March 2024 10:10pm
by AndyGable
thatbruce wrote: ↑Monday 25th March 2024 9:56pm
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)
Re: Get the Previous Sunday from a Date
Posted: Tuesday 26th March 2024 1:17am
by 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)
Re: Get the Previous Sunday from a Date
Posted: Wednesday 27th March 2024 1:44am
by AndyGable
BruceSteers wrote: ↑Tuesday 26th March 2024 1:17am
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)
Re: Get the Previous Sunday from a Date
Posted: Wednesday 27th March 2024 2:11am
by 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.
Re: Get the Previous Sunday from a Date
Posted: Wednesday 27th March 2024 2:43am
by AndyGable
BruceSteers wrote: ↑Wednesday 27th March 2024 2:11am
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"??
Re: Get the Previous Sunday from a Date
Posted: Wednesday 27th March 2024 3:55am
by 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")