sqlite SELECT the date WHERE theDate.Year = 2016 (for example)

Databases
1

I just cannot seem to be able to wrap my head around this question.

In an SQLITE SELECT Statement I have no trouble with “SELECT * FROM col WHERE id = 1”

But if I have a column containing an SQLDATE format, how is is possible to “SELECT theDate from Entries WHERE theDate.Year = 2016 AND theDate.Month = 10”

or in fact, have I just done it.

What I currently have in my database is a column for the SQLDATE, a column to hold the id of the years currently into another table and a column to hold the id of the month names in another table. All this sees very redundant, and anyway, once I retrieve theDate.Year and theDate.Month I have the answer to my other two columns in Xojo.

Hope all this makes sense, because it has been bugging me for weeks.

2

This should help Date function examples

3

Geez Peter, that was quick, I hardly had time to get back to the Forum Index.

I will look at your link. Thank You

4 
select * from table where strftime('%Y',date_field)='2016'
5

SELECT expenditure_id from t_expenditure where strftime(’%Y’,date_expenditure) = ‘2016’

Jean-Yves beat me :slight_smile:

Be sure in the where clause to put your value in single quotes as strftime returns a string.

6

Thanks Jean-Yves

I was about to reply again to Peter thanking him and telling him I probably had a month’s worth or study with what he linked me to. But now you example sorts it out for the year, so all else will fall into place.

I guess I can now remove those redundant columns from my database?

7

SELECT FROM table where datefield LIKE “2016-10%”

8

THIS! is why the date is formatted the way it is. C’mon people, there’s a reason for the standard. Don’t over complicate things. (Thanks Dave for inserting some sanity into this topic.)

9

Sorry Tim, I am assuming that you are saying this is a better method. If so, I certainly think it is clearer (maybe), Although it may be a moot point as once you have written the code, it is in place and if it works - it works.

10

Cliff. yes this IS the better solution, because the database engine is optimized to utilize this type of pattern matching with minimal overhead (not to mention the readabilty factor). ANY time you introduce functions (such as STRFTIME) into a select statement the EXPLAIN PLAN goes to h*ll in a handbasket very quickly. You may not notice any performance hit on a database with a few hundred records, but as the number of records increase the overhead will increase at a non-linear rate.

11

Thanks Dave

Currently the database in question holds just under 1500 rows, and row is added every day.

12

depending on you data extract needs, you might consider adding an INDEX by the data as well. INSERT on indexed tables suffer a minor performance hit, but since you are inserting only one per day (even a few thousand a day won’t matter), but the extract process (still using the LIKE syntax) will improve even more.

13

Thanks Dave (and everyone else). You have no idea how helpful all this is. I will look up INDEX in the Docs, and also all appropriate articles in XDEV.

Ain’t Xojo FUN!!!. It sure appeals to me, and I love using it (albeit as a complete amateur).

14

WARNING : do not go overboard on Indexes… they have their place, but the wrong index could lead to LOSS of performance not a gain… so be sure you take the data structure, the proposed index(es) and the types of queries (present and future) all into account

15

I thought about this one but when I tried I wrote :

SELECT FROM table where datefield = "2016%"

which definitely does not work becuse the % wildcard only work in conjunction with “like”