Can you format 24-hour time string into 12-hour time string with AM/PM?

Question

I store some time values in sqlite in %H:%M string format (for example "15:43"), but I would like to get them out formatted in 12 hour format with AM/PM indicators (for example "3:43 PM"). Is this possible with sqlite (and if so, how), or do I need to do this in my application code?

Answer 1

Some pseudo code to help you on the way:

if (hourpart of time >= 12)
    subtract 12 from hours
    append string " pm"
else // hourpart < 12
    append string " am"
end if

In SQL you can accomplish this using the CASE syntax.

---

After taking a closer look at the problem:

SELECT (CASE HOUR(myTimeColumn) >= 12 WHEN 1 THEN
         ((HOUR(myTimeColumn) - 12) + '-' + MINUTE(myTimeColumn) + ' pm')
       ELSE
         (HOUR(myTimeColumn) + '-' + MINUTE(myTimeColumn) + ' am')
       AS AmPmTime,
       someOtherColumn
FROM myTable

I'm not entirely sure that all of that is valid SQLite syntax, but you should be able to correct the bugs.

Answer 2

Do it in your application. Store it in normal 24h format in the database. In the database it can be stored as a Date entry instead of a string (correct me if im wrong)

Answer 3

Unless you extend sqlite with your own custom function, you'll have to do this is code.

sqlite's strftime date formatting function only supports a small subset of its C counterpart, insufficient for your problem. sqlite also lacks a selection construct like IF or CASE, making simple if/else impossible.

Answer 4

As PoweRoy recomended, this belongs in the application.

It is recommended that any kind of data stored of used in communication uses a standard, locale-insensitive format: http://www.mihai-nita.net/article.php?artID=20051025a