SQL [date] Code provides no results

Databases
1

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,
Tim

2

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’

3 
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

perhaps

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

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.

5

Thanks guys,
I will include a time parameter too.

Tim

6

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%’

7

Hello Dave,
What does the % mean?

Tim

8

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

9

Thanks Dave.

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

Tim

10

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.

11

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

Thanks for the tip Simon,
TIm

12

Simon,

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

Thanks again!
TIm

13

No. Use this:

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

Instead.

14

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