Currently when I search for this, sitepoint returns a blank page. And I couldn’t see it in the mysql docs. 
I have a time col with times stored as 04:30:00
Please can anyone help in how to format this to 4.30am and of course, to show pm, if appropriate.
bazz
OK, I got it by making a regex
bazz
thanks guido. I read that but have only just seen what it means 
well, I have got the hours part but the other %k, %I etc don’t seem to output what I would expect. Using the full range, they give back 12 0 12 12 12 for a time of 12:00:00
I shall read it again later coz it’s almost 7am here.
bazz
SELECT TIME_FORMAT('21:37',[COLOR="Red"]'%l:%i%p'[/COLOR])
9:37PM
Arghhhh! I wasn’t getting it to work because I had ’ ’ around the col name in the query.
now, how do I make AM PM into am pm without post processing?
I’ve tried the formatting ‘codes’ I am familair with but noe works. In this case it should, I thought, have been %P instead of %p
bazz
another unsuspecting victim of the misconception that anything (-cough- backticks -cough-) should ever be coded around a column name
SELECT LOWER(CAST(TIME_FORMAT('21:37','%l:%i%p') AS CHAR))
(:
lol.
No not backticks… I have never used them. it was like this
SELECT TIME_FORMAT('$start_time','%l:%i%p')
Thanks for the answer. wouldn’t have got that any time soon. 
bazz
why would you feed in $start_time and get mysql to format it?
why wouldn’t you format it in your app instead?
the only way the question made sense is if it was a column name
you’re not suppyling a variable column name are you?
no, I’m not supplying a variable column name. 
the time is stored as 22:30:00 for example and I was trying to dislpay it as 10.30pm
Are you saying that that is better done in my app code than at query stage?
bazz
as a general rule, absolutely yes, formatting of all sorts is best done in the application