Android Question SQLite queries (date syntax)

kanaida

Active Member
Licensed User
Longtime User
Hey guys, trying to do a simple query here where a date called delivery is >= some date.
But I keep getting results when I should get none. Can anyone clarify the proper usage of dates?

Currently I'm taking: 1/1/2014 (not sure about the number below)


B4X:
Dim date() As String = Regex.Split("/",tDelivery.Text)
Dim DateVal As Long = DateUtils.SetDate(date(2),date(0),date(1))

Then calling this query. All the dates in my db are currently 12/30/1899 (blank date for foxpro)

SELECT DISTINCT S.* FROM style S
JOIN linsheet L ON S.pat_code = L.line_patt
AND L.company_id = S.company_id
AND L.line_patt = S.pat_code
WHERE S.COMPANY_ID = 6
AND S.pat_disc = 0
AND L.line_disc = 0 
ORDER BY S.pat_code
AND L.delivery>=  1388552400000

SQLite database browser returns 0 records. The b4a sqlite returns a bunch, maybe all of them.
 

rbsoft

Active Member
Licensed User
Longtime User
"ORDER BY..." should be the last argument in your Select query.

Rolf
 
Upvote 0

devlei

Active Member
Licensed User
Longtime User
I would have also said the Last line (AND L.delivery>= 1388552400000) should come before the ORDER BY line.
 
Upvote 0

kanaida

Active Member
Licensed User
Longtime User
I didn't notice that because it was one long line :) didn't see it till i pressed enter a bunch of times and made it go on different lines.
It's so much easier when I have linq like in VS2012 lol

B4X:
Dim sql = <q>
UPDATE INVLINES
SET LN_INV_DAT=CTOD('<%= CDate(inv("inv_date")).ToShortDateString %>')
WHERE LN_INVOICE='<%= inv("inv_no") %>'
AND ln_inv_dat != CTOD('<%= CDate(inv("inv_date")).ToShortDateString %>')</q>.Value
 
Upvote 0

rbsoft

Active Member
Licensed User
Longtime User
ToShortDateString writes a string in "DD.MM.YYYY" Format. SQLite usually expects dates in "YYYY.MM.DD" Format (similar to good ol' dbase). This makes sorting very easy.

Your query might look like this (from your above example):
B4X:
SELECT DISTINCT S.* FROM style S
JOIN linsheet L ON S.pat_code = L.line_patt
AND L.company_id = S.company_id
AND L.line_patt = S.pat_code
WHERE S.COMPANY_ID = 6
AND S.pat_disc = 0
AND L.line_disc = 0
AND L.delivery> =  '2013-05-22'
ORDER BY S.pat_code

In your desktop application I would format the date like this:
B4X:
 .ToString("yyyy.MM.dd")
...to be compatible with SQLite standards.

Rolf
 
Upvote 0

kanaida

Active Member
Licensed User
Longtime User
this is the actual code:
B4X:
SELECT DISTINCT S.* FROM style S JOIN linsheet L ON S.pat_code = L.line_patt AND L.company_id = S.company_id AND L.line_patt = S.pat_code WHERE S.COMPANY_ID = 6 AND S.pat_disc = 0 AND L.line_disc = 0  AND L.delivery >= '2014.8.2' order by S.pat_code
 
Upvote 0

rbsoft

Active Member
Licensed User
Longtime User
Interesting, so sqlite can take a Long, or 2013.01.01 for example?
do I need quotes?

Sorry, I had made a mistake. It should be:
B4X:
.ToString("yyyy-MM-dd")
SQLite saves Date/Time usually as string in the format "yyyy-MM-dd hh:mm:ss". So in the SELECT query you would use:

L.delivery >= '2014-08-02'

Rolf
 
Upvote 0

kanaida

Active Member
Licensed User
Longtime User
lol. thanks. I'm used to sql and foxpro oledb. Everything is pretty much the same except for those stupid dates :)
It's working now. Since I was using the calendar i used the datetime class to parse out the year/month/day and format it properly.
 
Upvote 0

rbsoft

Active Member
Licensed User
Longtime User
Glad you got it running. I used Access databases in the past a lot. Working with dates was always terrible.

Rolf
 
Upvote 0
Top