Android Question Date Function with SQLite

[persianpowerman1]

hey guys this is a doubt... how to best solve an SQLite query that picks up a records BETWEEN two given dates...
I HAVE THE QUERY SYNTAX.. thats correct

but heres the problem

in this table i have 3 columns
ORDERid(TEXT)... this is actually the date the order is placed... is entered in "dd/mm/yyyy" format
FirstName(TEXT)... this is just a regular name of the buyer
Age(NUMBER)... this is a number for age of buyer

considering this "dd/mm/yyyy" format isnt followed by SQLite... and...
when i made the DB this very column (ORDERid) i declared as text...

what is the best way i could still run a query to get records that are between.. like eg.

 

[eps]

This won't work. Unless you get all the records and then order the list it returns.

As I'm sure you're aware you should store the date as a datetime field in SQLite and then you can access it. You could store it in the ORDERid (?) and ORDERdate as well, at the same time, in the correct format as well.

 

[LucaMs]

Probably esp is right, and maybe I am using the following method because I did not know SQLite.

I save dates in the db in the format yyyy/MM/dd; then use some functions for the "conversions" (setting DateTime.DateFormat = "yyyy/MM/dd" when I read from the db and then resetting the "global" DateFormat on exit).

In the query i can use something like:
"OrderDate >= '2013/01/01' AND OrderDate <= '2014/01/01'" (or use BETWEEN)

Maybe I misunderstood, but it seems to me that you are using a date as the primary key of the table. This is not a good choice.

 

[devlei]

A few ways I can think of quickly:

• Iterate through all the records in the DB, and for each convert the Text to Long, test it against your criteria, and of meets criteria, add it to a list or array.
  
• Write your whole database to a temp SQLite file adding another date field which is Numeric. As you add each record, convert the Text to Numeric for the new Date field. Then perform your query on the temp DB using the new Date field for the criteria.
• In my opinion it would be best to alter/redo your SQLite database and have your Date field as a Numeric variable.

 

[persianpowerman1]

ok... thanx... but listen up... cz we're gonna learn something...
this is how its done...

cursor1 = Main.SQL1.ExecQuery("SELECT orderId, name, age FROM sale WHERE DATE(substr(orderId,7,4)||substr(orderId,4,2)||substr(orderId,1,2)) BETWEEN DATE(20131201) AND DATE(20131231) ")

and you guys are welcome...

YO!han
HIT "like" or buy me a beer!

 

[eps]

Okay that may work, but performance will be terrible, I'd never put one substr in a SQL statement let alone 3!

 

[LucaMs]

I would prefer:

cursor1 = Main.SQL1.ExecQuery2("SELECT orderId, name, age FROM sale WHERE orderId BETWEEN ? AND ?", Args)

where Args is an array string of two elements, the dates you need.

This works if you use the format "yyyy/MM/dd" for date fields (TEXT)

You can use ORDER BY in your sql statements if you use that format !!!