Android Question Date Function with SQLite

P

persianpowerman1

Active Member
Licensed User
Longtime User
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.
 
Sort by date Sort by votes

eps

Expert
Licensed User
Longtime User
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

LucaMs

Expert
Licensed User
Longtime User
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:
Upvote 0
D

devlei

Active Member
Licensed User
Longtime User
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
P

persianpowerman1

Active Member
Licensed User
Longtime User
ok... thanx... but listen up... cz we're gonna learn something...
this is how its done...


B4X: 
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

eps

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

LucaMs

Expert
Licensed User
Longtime User
I would prefer:

B4X: 
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:
Upvote 0
You must log in or register to reply here.

Similar Threads

  • Article
Android Code Snippet Using SQLite
Replies
0
Views
246
Cadenzo
  • Article
B4A Class Convert DateTime, Graphic, Boolean, Units
Replies
2
Views
1K
Guenter Becker
  • Article
B4A Library TDDBUtils Functions to handle SQLite and SQLCipher
Replies
4
Views
2K
Guenter Becker
  • Article
B4A Library TDDButils Database Management for SQlite and SQLiteCipher
Replies
2
Views
3K
incendio
W
  • Question
Android Question sqlite "no such function" runtime error
2
Replies
27
Views
8K
Wong Ka Chun
W
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…