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?

Japeth

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.

Undervalue

Answer 2

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.

Textualist

I think you're missing the last '-' from that query: this worked for me: select rtrim (substr ('January--February-March----April----May------June-----July-----August---SeptemberOctober--November-December', strftime ('%m', 'now') * 9 - 8, 9), '-') Actually... I just saw that you suggest using spaces instead in which case you don't need to tell rtrim to remove -. – Schermerhorn

Answer 3

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.

Rask

What if my presentation layer is a text file from the output from a SQL statement? This is a very basic need and sad SQLLite doesn't support it out of the box. However given its goal and how lite it is I can accept this missed feature it just stinks. – Stribling

Answer 4

You can use the below query to fetch the date in the following format DD-Mon-YYYY using SQLite:

select strftime ('%d', 'now')||'-'||substr('--JanFebMarAprMayJunJulAugSepOctNovDec',strftime ('%m', 'now') * 3, 3)||'-'||strftime ('%Y', 'now') as date

Tabes

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Virulence