Android Question Get date of last day of the month

[james_sgp]

Hi,
I`m trying to generate a list of dates for the last days of the month, to use in a query for grouping data by month (based on a long field). I tried this but its not giving the expected result:

Sub build_dates
    Dim dt(12) As Long
    Dim tmp As Long
    Dim p1 As Period
    p1.Days = -1
    DateTime.DateFormat = "dd/mm/yyyy"
    For i = 1 To 12
        If sf.Len(i) = 1 Then
            tmp = DateTime.DateParse("01/0" & i & "/" & DateTime.GetYear(DateTime.Now))
            Log("tmp = " & DateTime.Date(tmp))
            
            dt(i) = DateUtils.AddPeriod(tmp, p1)
            Log("dt(i) = " & DateTime.Date(dt(i)))
        Else
            tmp = DateTime.DateParse("01/" & i & "/" & DateTime.GetYear(DateTime.Now))
            Log("tmp = " & DateTime.Date(tmp))
            
            dt(i) = DateUtils.AddPeriod(tmp, p1)
            Log("dt(i) = " & DateTime.Date(dt(i)))
        End If
    Next
End Sub

I`m getting this:

tmp = 01/01/2023
dt(1) = 31/01/2022
tmp = 01/02/2023
dt(2) = 31/02/2022
tmp = 01/03/2023
dt(3) = 31/03/2022
tmp = 01/04/2023
dt(4) = 31/04/2022
tmp = 01/05/2023
dt(5) = 31/05/2022
tmp = 01/06/2023
dt(6) = 31/06/2022
tmp = 01/07/2023
dt(7) = 31/07/2022
tmp = 01/08/2023
dt(8) = 31/08/2022
tmp = 01/09/2023
dt(9) = 31/09/2022
tmp = 01/10/2023
dt(10) = 31/10/2022
tmp = 01/11/2023
dt(11) = 31/11/2022
tmp = 01/12/2023

When I`m expecting dt(x) to be 31/12/2022, 31/01/2023, 28/02/2023...etc

 

[aeric]

You DateFormat is wrong. mm = minutes

DateTime.DateFormat = "dd/MM/yyyy"

Try:

Sub GenerateMonthEnd
    Dim yyyy As Int = DateTime.GetYear(DateTime.Now)
    DateTime.DateFormat = "dd/MM/yyyy"
    For mm = 1 To 12
        Dim FirstDay As Long = DateUtils.SetDate(yyyy, mm, 1)
        Log( DateTime.Date(FirstDay) )
        
        Dim days As Int = DateUtils.NumberOfDaysInMonth(mm, yyyy)
        Dim LastDay As Long = DateUtils.SetDate(yyyy, mm, days)
        Log( DateTime.Date(LastDay) )
    Next
End Sub

 

[james_sgp]

DOH.... of course...thanks Aeric ?

 

[Mahares]

Here is another method without involving DateUtils lib:

For i=1 To 12
        Log(LastDayofMonth(i, 2023))
    Next

Sub LastDayofMonth(m As Int, y As Int) As String
    DateTime.DateFormat = "dd/MM/yyyy"
    Select m
        Case 1, 3, 5,7,8,10,12
            Return $"31/${m}/${y}"$
        Case  4, 6, 9,11
            Return $"30/${m}/${y}"$
        Case Else
            If y Mod 4 = 0 Then
                Return $"29/${m}/${y}"$
            Else
                Return $"28/${m}/${y}"$
            End If
    End Select
End Sub

 

[Jeffrey Cameron]

I usually just add 1 to the month and then subtract one day:

Public Sub MonthEnd(Year As Int, Month As Int) As Long
    DateTime.DateFormat = "MM/dd/yyyy"
    Dim plDate As Long = DateTime.DateParse(Month & "/01/" & Year)
    plDate = DateTime.Add(plDate, 0, 1, -1)
    Return plDate
End Sub

 

[Mahares]

And if you want another method that involves SQLite here it is:

Sub DisplayLastDayOfMonthUsingSQLite(yr As Int)
    For i=1 To 12
        Dim s As String = IIf(i > 9, $"${yr}-${i}-01"$, $"${yr}-0${i}-01"$)
        Log(SQL.ExecQuerySingleResult($"SELECT strftime('%m/%d/%Y', '${s}','start of month','+1 month','-1 day')"$))
    Next
End Sub

log(DisplayLastDayOfMonthUsingSQLite(2023))

Output:
01/31/2023
02/28/2023
03/31/2023
04/30/2023
05/31/2023
06/30/2023
07/31/2023
08/31/2023
09/30/2023
10/31/2023
11/30/2023
12/31/2023