Strange php mysql date problem

i am having a small issue when saving date and time to mysql.

Mysql :

table field name : dt
mysql Data type : datetime

$dt=date('Y-m-d h:i:sa');
above code correctly prints the date and time for example : 2011-06-30 08:27:23pm

Problem is when saving to db, the Ante meridiem (a) is not added to the datetime in db . it just shows for example : 2011-06-30 08:27:23  only. no pm / am 

Why Ante meridiem is not saving?

btw, remember i use that dt field to sort the results by date and time.

mysql data type probably does not support that format. Try using varchar instead.

Hi, I believe the datetime should be in 24 hour format ie 08.27:23 or 20:27:23 rather than am or pm, try this:

date( 'Y-m-d H:i:s');

alexson is correct in this. Use the 24hour time structure, and it will save correctly.

BTW: If you’re trying to say ‘the current time’, MySQL has a couple of functions for that.

INSERT INTO table SET dt = NOW()

Use the 24hr format in your back-end and then format it as you like when displaying it.

thanks folks for the valuable replies!!

in addition to your valuable suggestions, i got another suggestion from one of my php friend and that also works for me. he sad to change datetime data type in mysql as int and save the date and time as time(). when reading back from db, use the date() to format it. i am happy with this since it works exactly as i expected.

Thank you all.