CODEWITHSUNDEEP
javaandroidsqlite
Tyler Treat
Tyler Treat

Reputation: 14998

Selecting rows where datetime column is a certain day

I'm trying to write a SQLite query in Android where I select all rows from a table where the day component of the datetime column (stored as ISO 8601 strings) equals a given day. Basically, I want to select all rows with a certain date, disregarding the time.

How can this be achieved?

Upvotes: 3

Views: 755

Answers (4)

Squonk
Squonk

Reputation: 48871

You can always use 00:00:00 and 23:59:59 as start and end times and simply use <= and >= in a raw query such as...

SELECT * FROM the_table WHERE the_datetime_column >= '2011-11-15 00:00:00' AND the_datetime_column <= '2011-11-15 23:59:59'

Upvotes: 0

Kurtis Nusbaum
Kurtis Nusbaum

Reputation: 30825

Use this query

select * from table_name where day=desired_day;

So this code:

database.query("table_name", null, "day like ?",new String[]{"____-__-daynumber%"}, null, null, null, null);

Checkout this documentation to see how I figured out that sql regex to use. This regex allows for any year and any month. If you want only a specific day in a specific month in a specific year do:

database.query("table_name", null, "day like ?",new String[]{"1983-03-22%"}, null, null, null, null);

This would look for the day 03/22/1983

Upvotes: 1

Wayne
Wayne

Reputation: 60414

SELECT * FROM test 
        WHERE strftime('%Y-%m-%d', timecol)=strftime('%Y-%m-%d', '2008-04-12')

Upvotes: 0

slkorolev
slkorolev

Reputation: 6001

SQLite has some date time functions. In your case the select could look as follows:

select * from table where date(your_date_time_column) = iso_8601_date_only_value;

Upvotes: 1

Related Questions