Date formatting from SQLite query

Question

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

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

And it returns:

03 03, 2009

I am trying to get it back:

March 3, 2009

Any suggestions?

Answer 1

SQLite does not support monthly names , unfortunately. You will need to convert it to the name of the month, either using the lookup table, or using the case statement or a switch at the presentation level.

Answer 2

It is absolutely true that SQLite is focused on functionality that belongs to the data storage layer, and NOT on functionality that belongs to the user interface, for example, formatting dates with month names, etc. I highly recommend you take this as a very useful hint to keep each type of functionality in its own layer! -).

If you are absolutely interested in improper use of the user interface functions at the data storage level, this can be somewhat wired, for example. having written my own formatting functions in C and plugging them into SQLite or having done incredibly awkward SQL algorithms, but I can’t imagine ANY use case where it would be worth the trouble.

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)) 

Dashes are used only to clear the page. Replace them with spaces.