After getting the date from mysql table how to change its format in php?

[mehidy]

Dear All

I have a table in my database, there is a date field in my table & it contain so many row with various date. When I get the output from my table its show as 2011-11-25, but I want to show date as November 25, 2011 & so on for every row.

Please help me to do it.

[chris_upjohn]

You would use either of the 2 following functions

date()
strftime()

Personally strftime works better for myself when formatting dates and times.

[mehidy]

Please show an example.

[smpily]

Mehidy: if you click those links it’ll take to examples and their definition. However, If I am reading correctly it’s not about changing the format of your date displayed its changing the format of stored time. In other words; 2011-03-04 to March 5, 2011. It would probably be easier to simply change the way its stored in the Mysql table but you could also use str_replace().

// str_replace searches a string for a match then replaces it with whatever.

// First break apart the $date_that_you_get_from_Mysql

$date_array = explode('-',$date_that_you_get_from_Mysql);

// $date_array[1] is now simply a numerical value. you could then run it thrugh a str_replace.. 

$month_array = array([1]='January','Febuary','March','So on','So Forth');
$month_nums = array('1','2','3',/*etc..*/);

$format = str_replace($month_array,$month_nums,$date_array[1]);

echo $format.'&nbsp'.date_array[2].','.$date_array[0];
// would echo March 4, 2011

PS I would fix the way the Data was stored. Much easier. But if that was not an option this would work.

[felgall]

@smpily

PS I would fix the way the Data was stored. Much easier.

No - you make it much harder if you don’t store dates as dates in a database. Storing them as text is just stupid as then you can’t even compare them when you want dates in a particular range.

If you always want the date in a specific format when you retrieve it from the database you can use the DATE_FORMAT() function in the actual SQL call to retrieve the date in whatever format you like - as long as you actually store it in the database as a date - CCYY-MM-DD is just the format that dates need to be in to save them as dates, you don’t necessarily have to retrieve them in that format (although that format does make it easier to process the dates within the PHP as well and you just convert it to DD-MM-CCYY before displaying it.

[mehidy]

@felgall

No - you make it much harder if you don’t store dates as dates in a database. Storing them as text is just stupid as then you can’t even compare them when you want dates in a particular range.

If you always want the date in a specific format when you retrieve it from the database you can use the DATE_FORMAT() function in the actual SQL call to retrieve the date in whatever format you like - as long as you actually store it in the database as a date - CCYY-MM-DD is just the format that dates need to be in to save them as dates, you don’t necessarily have to retrieve them in that format (although that format does make it easier to process the dates within the PHP as well and you just convert it to DD-MM-CCYY before displaying it.

thanks for your nice advise. will you please show me an example. I am just learning now.

[smpily]

@felgall

No - you make it much harder if you don’t store dates as dates in a database. Storing them as text is just stupid as then you can’t even compare them when you want dates in a particular range.

If you always want the date in a specific format when you retrieve it from the database you can use the DATE_FORMAT() function in the actual SQL call to retrieve the date in whatever format you like - as long as you actually store it in the database as a date - CCYY-MM-DD is just the format that dates need to be in to save them as dates, you don’t necessarily have to retrieve them in that format (although that format does make it easier to process the dates within the PHP as well and you just convert it to DD-MM-CCYY before displaying it.

Agreed. I didn’t say store it as text. I said change the way it’s formated. but ya you’ll want to use the mysql function DATE_FORMAT(); lots of examples on mysql.com as well.

$sql = "INSERT INTO post_date (post_dates) VALUES (DATE_FORMAT(NOW(),'%b %d %Y'))";

Happy coding

[mehidy]

@smpily

Agreed. I didn’t say store it as text. I said change the way it’s formated. but ya you’ll want to use the mysql function DATE_FORMAT(); lots of examples on mysql.com as well.

$sql = "INSERT INTO post_date (post_dates) VALUES (DATE_FORMAT(NOW(),'%b %d %Y'))";

Happy coding

Dear

I don’t want to change the format when I insert date in to my table. I want, when I will get the date from my table/publish date into my web page then it s/b show as “March3, 2011”. Hope you will help me on this.

[mehidy]

Please help me someone.

[felgall]

@smpily

Agreed. I didn’t say store it as text. I said change the way it’s formated. but ya you’ll want to use the mysql function DATE_FORMAT(); lots of examples on mysql.com as well.

$sql = "INSERT INTO post_date (post_dates) VALUES (DATE_FORMAT(NOW(),'%b %d %Y'))";

Happy coding

Dates are ALWAYS stored in the same format inside the database. DATE_FORMAT() should only ever be used in a select statement when extracting the date from the database (and only where you know that you are not going to need to do anything with it other than display it).

[smpily]

@felgall

Dates are ALWAYS stored in the same format inside the database. DATE_FORMAT() should only ever be used in a select statement when extracting the date from the database (and only where you know that you are not going to need to do anything with it other than display it).

Instead of quoting my text and saying how its wrong you could try to write it out too… I don’t mind being told I am in error thats how we learn. But you could contribute to the topic instead of quote/// no no no your wrong. Its seems a little diminutive.

once again I am gambling that fegall is right, and you would want to change your SQL statement to be something like this:

$sql = "SELECT DATE_FORMAT('%b %d %Y') FROM dates";

[r937]

this –

INSERT INTO post_date (post_dates)
VALUES (DATE_FORMAT(NOW(),‘%b %d %Y’))

won’t work, because date values going in to mysql must be given in year-month-day sequence

so best practice is to store dates and datetimes in the appropriate DATE or DATETIME datatype column

for retrieval, best practice is to do the formatting in the application – using DATE_FORMAT in the retrieval is of course possible, but that turns it into a string

further, if a change in format is desired, then you have to change the SQL, and this is, in many organizations, a more difficult change to get into production than to change the application code

besides, letting the application format the date allows for presenting it in one fashion to some viewers (e.g. brits) and in another for other users (e.g. yanks)