Date formatting from SQLite query

Question

I'm trying to pull and format timestamp from my SQLite db. In my reading, it seems that SQLite formatting is extremely limited compared to date formatting conversion available to PHP. I have this query coming from javascript:

SELECT strftime('%m %d, %Y', timestamp) AS saveddate

And it returns:

03 03, 2009

I'm trying to get it to return:

March 3, 2009

Any suggestions?

Answer 1

SQLite does not support month names, sadly. You will have to convert it to a month name either using a lookup table, a case statement, or a switch on the presentation layer.

Answer 2

It's absolutely true that SQLite is focused on the functionality that belongs in the data storage layer and NOT on functionality that belongs in the user interface, like formatting dates with month names and the like. I strongly suggest that you take this as a very helpful hint to keep each kind of functionality in its proper layer!-).

If you're absolutely keen to misplace UI functionality in the data storage layer, this can to some extent be shoehorned in, e.g. by writing your own formatting functions in C and hooking them up in SQLite, or going through incredibly clumsy SQL gyrations, but I cannot imagine ANY use case where that would be worth the bother.

Answer 3

Short month name

substr ("--JanFebMarAprMayJunJulAugSepOctNovDec", strftime ("%m", thedate) * 3, 3)

Full month name

rtrim (substr ("January--February-March----April----May------June-----July-----August---SeptemberOctober--November-December", strftime ("%m", thedate) * 9 - 8, 9))

The dashes are only used to make it clear on the page. Replace them with spaces.