Sqlite - check if today is between 2 dates

Question

I have a table that was defined like this:

db.execSQL("CREATE TABLE IF NOT EXISTS MyExercises (ID INTEGER, NAME VARCHAR, DESCRIPTION VARCHAR, VIDEONAME VARCHAR, IMAGENAME VARCHAR, VALIDFROM SMALLDATETIME, VALIDTO SMALLDATETIME, NUMOFREPS INTEGER, PRIMARY KEY(ID, VALIDFROM, VALIDTO));");

and i want to check if today is between VALIDFROM and VALIDTO.

When i insert the data to the table the dates look like this e.g.: 4/7/2013 12:00:00 AM ,4/27/2013 12:00:00 AM

INSERT INTO MyExercises VALUES (1,'blalbla','blabla', 'ex1.3gp', 'ex1.jpg', '4/7/2013 12:00:00 AM', '4/27/2013 12:00:00 AM' ,5)

but this query:

"SELECT NAME FROM MyExercises WHERE date('now') BETWEEN VALIDFROM AND VALIDTO"

doesn't return anything, even though today is between the dates from and to..

====================================================================

Solved it by changing all data to YYYY-MM-DD format

Thanks guys!

SELECT NAME FROM MyExercises WHERE date('now') BETWEEN strftime('%s', VALIDFROM) AND strftime('%s', VALIDTO) — Nadav, Apr 20 '13 at 12:14
Have u logged anything? do u know that date('now') function returns the current time. There may be timezone problem. check that — Sankar V, Apr 20 '13 at 12:17

score 0 · Answer 1 · answered Apr 20 '13 at 12:22

0

> 1.Insert two date(from,to)
> 2.Get all date between "from" and "to" date and store into arraylist or container //this step you may ignore also
> 3.check condition !from_Date.after(to_Date)

example

answered Apr 20 '13 at 12:22

QuokMoon

4,387
4
26
50

As a general rule, never do something in code, that can be done in the database directly. Why? Because the database can optimize a lot of stuff for you, and since you're working with a mobile application, there are limitations on your resources. If you do your `select` in database and only fetch the data that you actually need, you're better off with that. Don't create a lot of unnecessary objects. – Darwind Apr 20 '13 at 12:40

score 0 · Answer 2 · answered Apr 20 '13 at 12:25

0

You can try using this

db.query("MyExercises", null, TODAY + "BETWEEN ? AND ?", new String[] {
          VALIDFROM + " 00:00:00", VALIDTO+ " 23:59:59" }, null, null, null, null);

Here TODAY is today's date

answered Apr 20 '13 at 12:25

stinepike

54,068
14
92
112

Sqlite - check if today is between 2 dates

2 Answers2