1

I have a SQLite timestamp in the format of YYYY-MM-DDTHH:MM:SS (ex: 2013-07-26T08:01:05-04:00 ) I'd like to make a query that matches all entries on a particular day (2013-07-26)

From articles like this one I know that you can make greater than/less than compairson like this:

select * from foo created_at > "2013-07-25" and created_at < "2013-07-26";

But this seems weird and inefficient to program dynamically. What I'd like to do is have a query like the following:

select * from foo created_at = "2013-07-25";

or 

select * from foo created_at like "2013-07-25";

But both of those do not return any results. What am I missing?

Community
  • 1
  • 1
pca2
  • 165
  • 2
  • 8

1 Answers1

1

When doing date range queries when the field has a time component, always do this:

where YourDateField >= the first day of your range
and YourDateField < the day after the the last day of your range.

Everything else is a detail. Also, as long as you have a date or datetime datatype, don't worry about the format. The only time it's important is for displaying.

It may look inefficient because you have to do some extra typing, but the queries will run faster than using functions like this:

where somefunction(YourDateField) = something.
Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43