How to format date in SQLite according to current locale format settings?

Question

I need to match on a date using the LIKE operator. The date should be in current user's local format, not in the general format.

So I use the strftime function like this:

WHERE strftime('%d.%m.%Y %H:%M', createdDate, 'localtime') LIKE '%{searchTerm}%'

Unfortunately this works only for fixed format '%d.%m.%Y %H:%M'. But I want to use the user's current locale format.

So I need to either: 1) Get the strftime format string from a C++ locale object 2) Make SQLite format the date using current locale itself

Spent already several hours on this to no avail. Would be grateful if anyone would point me in the right direction on how to tackle this problem.

Example: Given current locale is German I want to get something like "%d.%m.%Y %H:%m". For an US locale I want to get "%m/%d/%Y %H:%m"

Answer 1

Normally the local date format can be obtained with the Windows GetDateFormat API (or GetDateFormatEx API for Vista). Your program could interrogate the API then transform the date accordingly. Following that, the date can be recorded in SQLite.

However, once can question the validity of storing timestamps in a specific format. That basically means a lot of code to manipulate each date, or no date manipulation at all. May I suggest, if it is possible, storing in a plain format (say ISO or UNIX timestamp) and working from that, outputing with whichever flavour of GetDateFormat is required?

Answer 2

OK, different answer.

Suppose you have your MyTable:

CREATE TABLE MyTable (
    MyPrimaryKeyHnd INTEGER PRIMARY KEY,
    ...
    CreatedDate TEXT);

(Where CreatedDate is in ISO format. I suppose you could also use a Unix timestamp. Your choice.)

Then a list of possible formats

CREATE TABLE TimeFormat (
    TimeFormatHnd INTEGER PRIMARY KEY,
    TimeFormatString TEXT NOT NULL,
    TimeFormatDescriptor TEXT);

You allow your user to chose a format of their choice and keep that in a seperate table or INI file. TimeformatString would be your strftime() compatible format string (such as '%d.%m.%Y %H:%M'). You just need to build your query with whatever the user's choice is.