Android Question SQL Select statement with date range condition

[DickyNAS]

Why the unwanted data can be displayed by SQL select statement in phpMyAdmin.

The data I selected should be between 01/09/2017 and 31/09/2017.

But 07/11/2017 data fields can also be displayed.

Any advise for this...

 

[Lahksman]

Could be the date format. Are you sure it's dd/MM/yyyy?

Personally I always store my dates as bigint (long in b4a) to avoid formatting issues.

 

[nwhitfield]

Is the field a MySQL Date format? Remember that the presentation you see in the admin interface isn't necessarily the same as what's stored in the database.

What if you rewrite the query with dates in ISO format, eg BETWEEN '2017-09-01' AND '2017-09-31' ?

Also, there isn't a 31st of September. Could that be causing the problem, because you're specifying an invalid end date? (And, come to that, if the CLI is parsing the date the US way, it'll read 01/09/2017 as 9th January, so may be querying for dates between then and 'invalid'.

What about having that condition as instead

AND MONTH(PO.Transaction_date) = 9

 

[OliverA]

If it is stored as a pure string, then that actually makes sense. 07/11/2017 is between 01/09/2017 and 30/09/2017. But that is pure conjecture until we know what field type PO.Transaction_Date is.