SQLite query help

1

Hi,
I have a table in the workflow repository that contains a column with a date in the format MM/DD/YYYY
I’d like to select rows based on the month equal to a static value like month = ‘12’. What is the correct query? I’ve tried using:

SELECT * FROM jobs WHERE strftime('%m', date_column) = '12'

but that isn't working.

thanks,
Lou
2

First of all, you shouldn’t use SQL statements to access the repository, you should use the API. If we switch the underlying architecture, the SQL will fail whereas the API will still work.

Second, the strftime() SQL method requires dates to comply with the standard ISO-8601 format, which your date doesn’t (re: the official documentation).

Third, you can achieve what you want with substr(date_column,1,2).

Finally, the following API statement will retrieve exactly what you’re asking for:

var jobs = repo.GetKeySets(“jobs”,‘’,“substr(date_column,1,2)=‘12’”);

3

Thanks Phil, sorry it wasn’t clear from my question but I am using the API to call GetKeySets().
The date comes in from another system in that non ISO format so I was stuck working with it as is. Does SQLite have a conversion function for dates to make them ISO compliant? Other libraries I’m using like amCharts also expect an ISO formatted date so it would be useful to be able to convert the date to the standard format.

4

As far as I know, there are no date conversion routines built in SQLite, other than those that require ISO formatting. I would think that the best way to handle this would be by inserting the proper values in the repository to start with. Make sure any date entered in the repo is ISO-compliant, which is easy to do with JavaScript code.

5

thanks. I threw in the towel and moved my data into MySQL where I can convert my data’s date to ISO format.