SQL [date] Code provides no results

Hello all,

The following code does not generate any results, even though there are 3 records available. Can anyone point to the problem?

SELECT transactions.unit_id, transactions.fname, transactions.lname, transactions.address, transactions.door_state, transactions.event_code, transactions.sql_date_time FROM transactions WHERE 
transactions.sql_date_time >= '2014-02-13'  AND transactions.sql_date_time <= '2014-02-13'    ORDER BY transactions.sql_date_time ASC ;

The date field is of type SQLite DATETIME field
Sample date for this field looks like this 2014-02-13 13:15:42.000

Any ideas?
Thank you,

No records could possibly match your criteria. Consider your sample value.

‘2013-02-13 13:15:42.000’ >= ‘2013-02-13’ // True
‘2013-02-13 13:15:42.000’ <= ‘2013-02-13’ // False

Your end time needs to be something like

transactions.sql_date_time <= ‘2014-02-13 23:59:59.999’

  • or -
    transactions.sql_date_time < ‘2014-02-14’

And bear in mind that this is strictly a string comparison, so you can even use

transactions.sql_date_time <= ‘2014-02-13ZZZ’

transactions.sql_date_time >= '2014-02-13'  AND transactions.sql_date_time <= '2014-02-13' 

Nothing is ever >= and <= a single value and since you have a time component… nothing is ever equal either


transactions.sql_date_time >= '2014-02-13 00:00:00.000'  AND transactions.sql_date_time <= '2014-02-13' 23:59:59.999"

Dave, you can compare date values to partial dates.

sql_date_time >= '2014' AND sql_date_time < '2015'

is legit and will match any record this year.

Thanks guys,
I will include a time parameter too.


I realize that… but the OP included month and day… so to find all records during a given day with a time compnonent …

another way is to use LIKE

transactions.sql_date_time like ‘2014-02-13%’

Hello Dave,
What does the % mean?


It is a wildcard.

The LIKE operator does partial comparison… so in the example I provided… it says "Give me everything that STARTS with ‘2014-02-13’

if you had “%xxx%” it would be “contains XXX”
and “%xxx” would be ends with xxx

Thanks Dave.

Adding “like” and the “%” helped too. Especially for single date searches.


I would also use the SQL between operator.

select x from y where tdate between '2014-02-13' and '2014-02-21'

This is much more efficient.

Hi Simon,
So ‘between’ is a key word too… I will do some searching on that.

Thanks for the tip Simon,


With the between keyword, does that eliminate the need to include a TIME component in my case?

Thanks again!

No. Use this:

select x from y where tdate between '2014-02-13 00:00:00' and '2014-02-21 23:59:59'


you can use datetime( transactions.sql_date_time ) to be sure that its format is without partial seconds