How to do less than equal to for date in sqlite

Question

When I try to run the below query I get no result even though the date time is same as that in the table.

The BillDateTime column is of TEXT Data type

SELECT * FROM BillingTransaction WHERE BillDateTime <= datetime('2021-09-19 07:21:31.371766')

BillNo	VechicleNo	BillDateTime	LoadType	BillAmount	FirstWeight	SecondWeight
1	ka04sdfl	2021-09-19 07:21:31.371766	EMPTY	30	400	0

@KenWhite `datetime()` does not convert a string to date because there is no date/ datetime data type in SQLite: https://www.sqlite.org/datatype3.html — forpas, Sep 19 '21 at 10:31

score 1 · Answer 1 · answered Sep 19 '21 at 07:41

The function datetime() strips off everything after the seconds, because it is equivalent to:

strftime('%Y-%m-%d %H:%M:%S', ...)

The result of:

SELECT datetime('2021-09-19 07:21:31.371766')

is:

2021-09-19 07:21:31

So your code is equivalent to:

SELECT * FROM BillingTransaction WHERE BillDateTime <= '2021-09-19 07:21:31'

and this is why you don't get any rows.

You don't need the function datetime(), because the values of the column BillDateTime are strings in the proper ISO format.

You can do direct comparisons with them:

SELECT * 
FROM BillingTransaction 
WHERE BillDateTime <= '2021-09-19 07:21:31.371766'

1 Answers1