PHP/MySQL: Convert from YYYY-MM-DD to DD Month, YYYY?

Question

Hi, I have in a MySQL table a DATE column that represents the date in this format: YYYY-MM-DD.

I wanto to retrieve the date from the database using PHP but display it like this: DD Month, YYYY.

From '2009-04-13' to '13 April, 2009' for example.

Witch is the best way to do it?? ( I know how to get the date from the DB. I only need to know how to convert it)

I also need to display the month names in Spanish. There is a way to do it without translating each month using strplc or something like that??

I'm new to programming, please be detailed.

Thanks!!!

Answer 1

Refer to DATE_FORMAT() function in MySQL. I guess that's the best way for you to do it.

Also, you can make this:

• Fetch your date from DB
• Use strtotime in PHP, to convert to unix time
• Then format the time using date.

By using date() you'll be able to get months names in Spanish when you set your locale in PHP with setlocale.

Answer 2

Simplest way is to use the strtotime() function to normalize the input to UNIX timestamp.

Then use the date() function to output the date in any format you wish. Note that you need to pass the UNIX timestamp as the second argument to date().

Answer 3

I'd do it in PHP, not in MySQL. Does using PHP's DateTime::format give you trouble?

Answer 4

Execute following MySQL queries:

SET lc_time_names = 'es_ES';
SELECT DATE_FORMAT(t.date,'%e de %M, %Y') FROM your_table t ...

With MySQLi it'll be:

$mysqli->query("SET lc_time_names = 'es_ES'");
$stmt = $mysqli->prepare("SELECT DATE_FORMAT(t.date,'%e de %M, %Y') FROM your_table t ...where id = ?");
...

Answer 5

Another option not yet mentioned:

SQL:

SELECT UNIX_TIMESTAMP(date) FROM table

PHP:

print date('your format', $timestamp_from_the_db);

Answer 6

Personally, I like to use integer data types in MySQL for date storage in the UNIX timestamp format. I leave all the processing of that integer up to PHP. Keeping tables and queries as simple as possible has always served me well. Predominantly, in the code I write, dates have some sort of calculation done to them. This is all done on the PHP side and always in the UNIX timestamp format. Storing or retrieving the dates in anything other than the UNIX timestamp format just means another step for errors to creep in and makes the query less modular. How a date is formatted is best left up until the last minute before it's displayed. It's just my opinion, but unless there are extreme circumstances where you can't process the DB value after extraction, a date shouldn't be formatted SQL-side.

A simplified example:

<?php

$date = now();
$dueDate = $date + 60*60*24*7; // One week from now

$sqlInsert = "INSERT INTO reports SET `dueDate` = $date";
$resInsert = mysql_query( $sqlInsert );

$sqlSelect = "SELECT `dueDate` FROM reports";
$resSelect = mysql_query( $sqlSelect );
$rowSelect = mysql_fetch_array( $resSelect );

$DB_dueDate = $rowSelect['dueDate'];

$daysUntilDue = ( $DB_dueDate - now() ) / 60*60*24;

$formattedDueDate = date( "j F, Y", $DB_dueDate );

?>

The report is due on <?=$formattedDueDate?>.  That is <?=$daysUntilDue?> from now.

Answer 7

You could also skip the strtotime() part by using UNIX_TIMESTAMP(date) in your MySql select. But remember that this is a MySQL specific function and may not be be portable in the future.