Php echo correct date but doesnt insert


when i echo this code

$mddate = date("d-m-Y h:i a");
echo $mddate;

it outputs the correct date format

19-08-2016 04:07 pm

but when insert it in database then it inserts
0000-00-00 00:00:00

i have date column configured as datetime in mysql phpmyadmin


As you can probably tell, the mysql’s datetime format, which is

0000-00-00 00:00:00 

doesn’t look very similar to your value, which is

19-08-2016 04:07 pm

As soon as you create your date in the proper format, mysql will be happy to accept it


hi colshrapnel

this time i tried

$mddate = date("Y-m-d h:i:sa");

and it inserted

2016-08-20 02:33:21

how can i remove the seconds. i dont want seconds.

also i want to add am/ pm


The correct format for mysql is

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

Which will look like

2016-08-19 16:07:00

This is the format that mysql uses, it is 24 hour clock, no am/pm and it includes seconds.
If you want another format, you must convert it to your preferred format after retrieving it from the database.


hi sama74

if i store it as
2016-08-19 16:07:00

will i be able to echo it in am/pm format later ??

it will done in mysql select query or php ??


Yes, that’s the point of having a proper format in a database - you can convert or modify it any way later.

You can do it both in mysql and PHP. to do it in mysql

SELECT DATE_FORMAT(date_field, '%d-%m%Y %H:%i %a') as formatted_date

to do it in PHP

$mddate = date("d-m-Y h:i a", strtotime($row['date_field']));

thanks colshrapnel

for the date solutions


hi colshrapnel

with mysql select query “%a” is echoing weekdays mon,tue,wed

it didnt echo am/pm

but in php “a” echoes am/pm

so “a” behave differently in mysql and differently in php

i again tried

SELECT DATE_FORMAT(date_field, '%d-%m-%Y %r') as formatted_date

it echoed
21-08-2016 02:58:07 AM

so how do i remove the seconds from the above time in select query statement
but keep the “am/pm”


replace %r with %H:%i %a

i my post #8 i have already written that %a echoes mon,tue,wed

i need am/pm without seconds


I meant %H:%i %p - sorry hit the wrong letter on the keyboard due to trying to do too many things at once.

yes this is giving correct result

thanks felgall

this question you can easily answer yourself, without the need to wait for a reply on a forum.

just click on your browser’s address bar and type mysql date_format there. then hit enter.
on the page that opens click the first link.
there will be the list of all options available where you may pick one that suits you best.

give it a try - it’s safe and real fast!

thanks colshrapnel
for the suggestion


This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.