P persianpowerman1 Active Member Licensed User Longtime User Jan 21, 2014 #1 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.
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 Expert Licensed User Longtime User Jan 21, 2014 #2 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. Upvote 0
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 Expert Licensed User Longtime User Jan 21, 2014 #3 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. Last edited: Jan 21, 2014 Upvote 0
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.
D devlei Active Member Licensed User Longtime User Jan 21, 2014 #4 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. Upvote 0
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.
P persianpowerman1 Active Member Licensed User Longtime User Jan 22, 2014 #5 ok... thanx... but listen up... cz we're gonna learn something... this is how its done... Code: Copy 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! Upvote 0
ok... thanx... but listen up... cz we're gonna learn something... this is how its done... Code: Copy 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 Expert Licensed User Longtime User Jan 22, 2014 #6 Okay that may work, but performance will be terrible, I'd never put one substr in a SQL statement let alone 3! Upvote 0
Okay that may work, but performance will be terrible, I'd never put one substr in a SQL statement let alone 3!
LucaMs Expert Licensed User Longtime User Jan 22, 2014 #7 I would prefer: Code: Copy 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 !!! Last edited: Jan 22, 2014 Upvote 0
I would prefer: Code: Copy 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 !!!
