Get dates for last week

S

sconlon

Active Member
Licensed User
Longtime User
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.

B4X: 
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.
 
Sort by date Sort by votes

Erel

B4X founder
Staff member
Licensed User
Longtime User
Here:
B4X: 
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
 
Upvote 0

mc73

Well-Known Member
Licensed User
Longtime User
If this is about using sql, you can also give the following a try:
B4X: 
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.
 
Upvote 0
You must log in or register to reply here.
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…