SQLite code traps, but we saved the “DATE” !

Dimitris Rebis | 19/09/2016 | pocketbiz, Sales Force Automation, SQL, SQlite

SQLite code traps, but we saved the “DATE” !

As a new PocketBiz Implementor/er I have had to write some sqlite queries for the pda's reports.


As an old programmer, to learn this new little "strange" for me sql tool, I performed the classic technique: copy-paste-read-learn-modify.

I found randomly and unluckily a similar code from an older implementation and start trying modifications.

Bad Luck! I copied a small piece of garbage inside my code.  

This: … WHERE dockatahorisidate > (DATE ('now') - 180)

I supposed that this expression returns the date, before 180 days from now.

Break with the code!

The correct syntax is: DATE ('now','-180 day')


And another try on the road to disaster… (finally success!!)

DATE(‘now’) returns current date like 2016-07-11

Using the function Strftime(‘%Y’,DATE(‘now’)) we get only the year value: 2016

In the following example we get the last year (current year – 1)

But when we put it on the full sql statement, the output is …Nothing, although soYear field has the desired “2015” value!

The Correct syntax is the following:

WHERE CustomTimologia.cusid=$cusid$ and

soYear=strftime('%Y', DATE ('now','-1 years')) and

soPeriod=(case when strftime('%m', 'now')<='04' then 1

when (strftime('%m','now')>'04' and strftime('%m','now')<='08') then 2

when (strftime('%m','now')>'08' and strftime('%m','now')<='12') then 3


group by soYear, soMonth


The rest piece of the code, demonstrates the soPeriod creation (quarter months) using “case when” and the ‘%m’ parameter to strftime function that returns the month..


what is sqlite

SQLite is an opensource “lite” SQL database that stores data to a text file on a device. Android comes with built in SQLite database implementation.

SQLite supports all the relational database features. Sqlite syntax looks like sql but it has a lot of differences…


PocketBiz, uses SQLite as SQL server database alternative for offline data manipulation in the Android environment.

Massive tests and quality control procedures ensure the quality of the final implementation of PocketBiz New Era.


More information about sqlite @ https://en.wikipedia.org/wiki/SQLite

More about sqlite date time functions @ https://www.sqlite.org/lang_datefunc.html

More information about PocketBiz @ http://www.sieben.gr/el/pocketbiz/