Android Question Bug that was difficult to spot

[RB Smissaert]

Spent quite a lot of time spotting the problem in the following code fragment:

Sub UpdatePatientsAge
 Dim i As Int
 Dim iNewDBDate As Int
 Dim iDOB As Int
 Dim strSQL As String
 Dim RS1 As ResultSet
 Dim lstIDAge As List
 Dim lTicks As Long
 Dim iNewDBDay As Int
 Dim iNewDBMonth As Int
 Dim iDOBDay As Int
 Dim iDOBMonth As Int
 strSQL = "select int_val from settings where name = 'NEW_DB_DATE'"
 iNewDBDate = SQL1.ExecQuerySingleResult(strSQL)
 lTicks = (iNewDBDate - 25569) * 86400000 - 3600000
 iNewDBDay = DateTime.GetDayOfMonth(lTicks)
 iNewDBMonth = DateTime.GetMonth(lTicks)

Problem was that iNewDBDay and iNewDBMonth got the wrong value, think it was 2 and 1.
There is no crash or error at all.
Dates (iNewDBDate) are Excel dates so values like 43817 (today's date).
I take it this will be easy to spot for more experienced B4A users.
It took me a long time as all works fine if you step through the code in debug mode.

RBS

 

[rraswisak]

43817 value in excel is date serial number, so you must convert this serial number first to date value:

Sub GetDateFromDateSerial(sn As Long) As Long
   Dim mm, dd As Int
   Dim theDate As String
   For y = 1900 To DateTime.GetYear(DateTime.Now)
       For m=1 To 12
           mm = DateUtils.NumberOfDaysInMonth(m,y)
           For d=1 To mm
               dd = dd + 1
               If dd = sn Then
                   'M/D/Y date format
                   theDate = m & "/" & d & "/" & y
                   Exit
               End If
           Next
       Next
   Next
   Return DateTime.DateParse(theDate)
End Sub

Example:

Dim dt As Long = GetDateFromDateSerial(43817)
Log("DAY: " & DateTime.GetDayOfMonth(dt))
Log("MONTH: " & DateTime.GetMonth(dt))
Log("YEAR: " & DateTime.GetYear(dt))

Reff: here

 

[RB Smissaert]

43817 value in excel is date serial number, so you must convert this serial number first to date value:

Sub GetDateFromDateSerial(sn As Long) As Long
   Dim mm, dd As Int
   Dim theDate As String
   For y = 1900 To DateTime.GetYear(DateTime.Now)
       For m=1 To 12
           mm = DateUtils.NumberOfDaysInMonth(m,y)
           For d=1 To mm
               dd = dd + 1
               If dd = sn Then
                   'M/D/Y date format
                   theDate = m & "/" & d & "/" & y
                   Exit
               End If
           Next
       Next
   Next
   Return DateTime.DateParse(theDate)
End Sub

Example:

Dim dt As Long = GetDateFromDateSerial(43817)
Log("DAY: " & DateTime.GetDayOfMonth(dt))
Log("MONTH: " & DateTime.GetMonth(dt))
Log("YEAR: " & DateTime.GetYear(dt))

Reff: here

This is not the problem.
If you step through the code in debug mode
it will work perfectly fine.

RBS

 

[rraswisak]

This is not the problem.
If you step through the code in debug mode
it will work perfectly fine.

okay, 2nd change...

1. declare iNewDBDate variable As Long
2. change 25569 value with 25568

 

[RB Smissaert]

okay, 2nd change...

1. declare iNewDBDate variable As Long
2. change 25569 value with 25568

Number one was exactly it.
No idea why it runs fine if stepping through in debug mode.
Same problem occurs if compiled in debug mode, but not stepping through.

RBS

 

[RB Smissaert]

Spent quite a lot of time spotting the problem in the following code fragment:

Sub UpdatePatientsAge
 Dim i As Int
 Dim iNewDBDate As Int
 Dim iDOB As Int
 Dim strSQL As String
 Dim RS1 As ResultSet
 Dim lstIDAge As List
 Dim lTicks As Long
 Dim iNewDBDay As Int
 Dim iNewDBMonth As Int
 Dim iDOBDay As Int
 Dim iDOBMonth As Int
 strSQL = "select int_val from settings where name = 'NEW_DB_DATE'"
 iNewDBDate = SQL1.ExecQuerySingleResult(strSQL)
 lTicks = (iNewDBDate - 25569) * 86400000 - 3600000
 iNewDBDay = DateTime.GetDayOfMonth(lTicks)
 iNewDBMonth = DateTime.GetMonth(lTicks)

Problem was that iNewDBDay and iNewDBMonth got the wrong value, think it was 2 and 1.
There is no crash or error at all.
Dates (iNewDBDate) are Excel dates so values like 43817 (today's date).
I take it this will be easy to spot for more experienced B4A users.
It took me a long time as all works fine if you step through the code in debug mode.

RBS

This had to do with updating an age field in SQLite based on a date of birth field holding Excel dates.
I was doing this in code, but there is a much simpler way to do this in SQL and here the code, for
in case somebody finds that useful:

Sub UpdatePatientsAge
 Dim strSQL As String
 strSQL = $"update patients set age = (strftime('%Y', 'now') -
strftime('%Y', ((datetime((dob - 25569) * 86400 - 3600, 'unixepoch'))))) -
(strftime('%m-%d', 'now') < strftime('%m-%d', ((datetime((dob - 25569) * 86400 - 3600, 'unixepoch')))))"$
 SQL1.ExecNonQuery(strSQL)
End Sub

Only takes some 180 milli-secs for 12000 rows.

RBS