Getting The Dates Of The Current Week Question?

General
1

Hi,

I am trying to create a database search between two dates and I want to have a button that basically says “Search Current Week.” I have two text boxes called

StartDate and the other EndDate. It would be nice to have StartDate fill in with the current week Sunday date, and the EndDate fill in with the current week Saturday date when you clicked the button.

I was hoping there was an incredible easy way to do this, but I can’t seem to find anything.

I know you can get the current day of the week by using :

str(d.dayofweek)

But then I would have to do a whole bunch of math to get the correct start and end dates. It would be tricky when months overlap, and years with leap years.

Does anyone know if there is an easy way to accomplish this.

I have another question as well. I am using the d.SQLDate to do the search in the SQLite database. Is there a way to take that d.SQLDate and get what the day of the week would be?

In other words my date is 2014-03-26. Is there code that can be used so I can pull out the day of the week and put in a textbox that it is a Wednesday?

Any help would be greatly appreciated.

Jim

2

Date has a property called DayOfWeek. It returns 1 for Sunday, 7 for Saturday.

3

Hi Jim,

As Eli points out, you could use the DayOfWeek property on Date object.

Another idea would be to create a Calendar Table in your database, here is an article on the idea. Linking such a table to an events table for instance would allow you to do simple select queries that return:

  • all the evens that occur in a given week
  • all the events that occur at the weekend in a given quarter
    and so on

SQLite has a set of date functions that will allow you to create such a table.

4

Hi Eli,

I know about the property day of the week, but that just gives you the value 1 - 7. I have that listed above. What I need is the actual date for that week.

For instance, the code below will come back as “4” because this is the 4th day of the week.

dim d as new date
MsgBox (str(d.dayofweek))

But I need to know what the actual date of Sunday will be. That is not easy. Like next week Sunday will be March 30 and Saturday will be April 5. You can’t get that information by simply knowing that Wednesday is 4. I guess there is no easy way to do that.

I was wondering if there was some builtin Xojo functions that will tell me that if today’s date is 2014-03-26 then the StartDate and EndDates for this week would be: 2014-03-23 and 2014-03-29.

5

Hi James,

Thanks for taking the time to help me. I’m afraid that calendar table is going to be quite a bit over my head to take on. I’m not sure I could pull that off.

6

Try this:

  dim d as new date
  dim dayNum as Integer = d.DayOfWeek
  dim firstDay as new date
  firstDay.TotalSeconds = d.TotalSeconds - (dayNum-1)*24*60*60 'Gives previous Sunday
  dim lastDay as new date
  lastDay.TotalSeconds = first.TotalSeconds + 6*24*60*60 'Gives coming up Saturday

Note, this seems like it should work for all cases for me, but I haven’t really thought it through all that much. LOL But since you’re doing this through date objects, it should take care of different months, leap years, etc.

7

I was literally about to paste almost the same code!

[code] dim startOfWeek As new Date //starts as now
dim endOfWeek As new Date// starts as now
dim today As new date //starts as now

//subtract dayOfWeek times that many days worth of seconds
startOfWeek.TotalSeconds=startOfWeek.TotalSeconds-((today.DayOfWeek-1)2460*60)

//add 6 days of seconds
endOfWeek.TotalSeconds=startOfWeek.TotalSeconds+(62460*60)
break[/code]

8

Hi Bill and Jim,

Thanks so much for taking the time to help me. That worked perfect. Its exactly what I needed!! Thanks so much for your help.

Jim

9

Feeling quite dumb today…and if the start of week is monday?

10

Add 1 to the day part of both dates?

11

Wouldn’t that give me monday of next week if the current date is a sunday?

12

I have two methods in a module that will do what you want:

[code]Const SecsInDay = 86400

Function MondayThisWeek(extends dt As Date) As Date
dim ret As new Date(dt)
ret.Second = 0
ret.Minute = 0
ret.Hour = 0

while ret.DayOfWeek <> 2
ret.MinusDay
wend

Return ret

End Function

Sub MinusDay(Extends dt As Date, optional NumDays As Integer = 1)
dt.TotalSeconds = dt.TotalSeconds - (SecsInDay * NumDays)
End Sub
[/code]

13

I’ll give that a go! Thanks @Simon Berridge.

14

Works great! Thanks again :slight_smile:

15

Could someone post an example code

Thanks.

Lennox

16

It’s right above :slight_smile:
https://forum.xojo.com/conversation/post/241648

Create the constant and the two methods in a module.
Then use it like this for example:

Dim d as New Date
MsgBox d.MondayThisWeek.SQLDate
17

Thanks Albin,

That is the part I could not figure out.

One problem though… It gives 2016-01-11 which is correct for weeks starting on Monday
How can I fix that for weeks starting Sunday?

Thanks again.

Lennox

18

[quote=241686:@Lennox Jacob]Thanks Albin,

That is the part I could not figure out.

One problem though… It gives 2016-01-11 which is correct for weeks starting on Monday
How can I fix that for weeks starting Sunday?

Thanks again.

Lennox[/quote]

http://documentation.xojo.com/index.php/Date.DayOfWeek

19

Thanks Michel, I see that

I now see what this code is all about.

Thanks again.

Lennox

20

[quote=241686:@Lennox Jacob]Thanks Albin,

That is the part I could not figure out.

One problem though… It gives 2016-01-11 which is correct for weeks starting on Monday
How can I fix that for weeks starting Sunday?

Thanks again.

Lennox[/quote]
The supplied methods assume a Monday (the method name is a giveaway!). The code part that assumes Monday is:

while ret.DayOfWeek <> 2 ret.MinusDay wend
Just change the 2 to 1 for a Sunday, 3 for a Tuesday etc.