Get dates for last week

[sconlon]

Has anyone a good method of getting the dates (as longs) for the start (Monday at 00:00) and end (Sunday at 00:00) of last week? I'm using these in an SQL query to get records that were created anytime last week.

I have been trying the following but it always seems to give me a start date of 24/3 at 23:00.

Dim now, thisday, midnight, swlong, sdlong, edlong as long
Dim daynum, numdays as int
Dim thisday as string

   now = DateTime.now
   thisday = DateTime.Date(now)
   midnight = DateTime.DateParse(thisday)
   daynum = DateTime.GetDayOfWeek(now)
   If daynum = 1 Then 
      numdays = 6
   Else 
      numdays = daynum - 2
   End If
   swlong = midnight - numdays*DateTime.TicksPerDay    ' start of this week
   SDlong = (swlong+1) - 7*DateTime.TicksPerDay          ' start of last week
   EDlong = swlong - 1                                               ' end of last week

Thanks for any help.

 

[Erel]

Here:

Sub Activity_Create(FirstTime As Boolean)
   Dim LastSunday As Long = GetPreviousDay(1)
   Dim Monday As Long = DateTime.Add(LastSunday, 0, 0, -6)
   'You probably want to set the time of LastSunday to 23:59:59
   'LastSunday = DateUtils.SetDateAndTime(DateTime.GetYear(LastSunday), DateTime.GetMonth(LastSunday), _
   '   DateTime.GetDayOfMonth(LastSunday), 23, 59, 59)
   Log("Monday: " & DateUtils.TicksToString(Monday))
   Log("Sunday: " & DateUtils.TicksToString(LastSunday))
End Sub

'1 -> Sunday, 7-> Saturday
Sub GetPreviousDay(DayOfWeek As Int) As Long
   Dim day As Int = DateTime.GetDayOfWeek(DateTime.Now)
   If day <> DayOfWeek Then
      day = (day + 7 - DayOfWeek) Mod 7
   End If
   Dim d As Long = DateTime.Add(DateTime.Now, 0, 0, -day)
   Return DateUtils.SetDate(DateTime.GetYear(d), DateTime.GetMonth(d), DateTime.GetDayOfMonth(d))
End Sub

 

[sconlon]

Many thanks Erel - that is certainly a neat bit of code!

 

[mc73]

If this is about using sql, you can also give the following a try:

Dim findcurrentWeekNumberQuery As String 
    findcurrentweeknumberquery="select strftime('%W','now')"
    Dim currentWeekNumber As Int 
    currentweeknumber=ansql.ExecQuerySingleResult (findcurrentWeekNumberQuery)
    Msgbox(currentWeekNumber,"")

By subtracting one, you can get the previous week's number, then perform the desired queries. However, you have to be careful at week number 0 (first week of January), since the previous one, can't be -1. Furthermore, in your query, you have to supply also the year, since by quering just the weekNumber, you will end up getting records from various years with the same weekNumber.