Converting Date Formats?

Kit

Howdy!

I have some dates in a MySQL database that are in a 'Y-m-d' format.
Is there a way to have PHP read these dates and convert them to a
'd-M-Y' format?

Jul 17 '05 #1

Tom Thackrey

On 11-Nov-2003, ad****@yahoo.com (Kit) wrote:

I have some dates in a MySQL database that are in a 'Y-m-d' format.
Is there a way to have PHP read these dates and convert them to a
'd-M-Y' format?

You can use mysql to convert them to some other format with the
DATE_FORMAT() function or, if they're in the UNIX timestamp date range, you
can use the mysql UNIX_TIMESTAMP or the PHP strtotime() function to convert
them to a UNIX timestamp and the date() function to convert the timestamp to
anything you want. for example:

select *, date_format(datefield,'%d-%m-%Y') as dmydate from ...

or

$dmydate = date('d-m-Y',strtotime($row['datefield']));

--
Tom Thackrey
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to ja*********@willglen.net (it's reserved for spammers)

Jul 17 '05 #2

Alvaro G Vicario

*** Kit wrote/escribió (11 Nov 2003 23:15:42 -0800):

I have some dates in a MySQL database that are in a 'Y-m-d' format.
Is there a way to have PHP read these dates and convert them to a
'd-M-Y' format?

There are many ways. For instance, database can make the task for you:

select date_format(NOW(), '%d-%m-%Y')

--
--
-- Álvaro G. Vicario - Burgos, Spain
--

Jul 17 '05 #3

Michael Fuhr

ad****@yahoo.com (Kit) writes:

I have some dates in a MySQL database that are in a 'Y-m-d' format.
Is there a way to have PHP read these dates and convert them to a
'd-M-Y' format?

Here's one way, assuming that the 'Y-m-d' date is in the variable
$ymd:

$dmy = date('d-M-Y', strtotime($ymd));

Is there a reason you can't have MySQL format the date the way you
want it with DATE_FORMAT()?

mysql> select birthday from foo;
+------------+
| birthday |
+------------+
| 1997-01-12 |
+------------+
1 row in set (0.00 sec)

mysql> select date_format(birthday, '%d-%b-%Y') from foo;
+-----------------------------------+
| date_format(birthday, '%d-%b-%Y') |
+-----------------------------------+
| 12-Jan-1997 |
+-----------------------------------+
1 row in set (0.00 sec)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Jul 17 '05 #4

Tony Marston

ad****@yahoo.com (Kit) wrote in message news:<e7**************************@posting.google. com>...

Howdy!

I have some dates in a MySQL database that are in a 'Y-m-d' format.
Is there a way to have PHP read these dates and convert them to a
'd-M-Y' format?

Take a look at http://www.mysql.com/doc/en/Date_and...functions.html
particularly the bit about the DATE_FORMAT function.

Or take a look at http://www.tonymarston.net/php-mysql/dateclass.html
which describes a PHP class which can validate and format dates.

Tony Marston
http://www.tonymarston.net/

Jul 17 '05 #5

Markus Ernst

"Michael Fuhr" <mf***@fuhr.org> schrieb im Newsbeitrag
news:3f********@omega.dimensional.com...

ad****@yahoo.com (Kit) writes:
Is there a reason you can't have MySQL format the date the way you
want it with DATE_FORMAT()?

If you want to order by the date you can't do that.

Though the other suggestions are more elegant you might like that solution
for your special case:

$d = explode("-", $date);
$formatteddate = $d[2]."-".$d[1]."-".$d[0];

HTH
Markus

Jul 17 '05 #6

Michael Fuhr

"Markus Ernst" <derernst@NO#SP#AMgmx.ch> writes:

"Michael Fuhr" <mf***@fuhr.org> schrieb im Newsbeitrag
news:3f********@omega.dimensional.com...

Is there a reason you can't have MySQL format the date the way you
want it with DATE_FORMAT()?

If you want to order by the date you can't do that.

If you want to sort by date in PHP then changing the date format
can make the sorting awkward, but using ORDER BY in the query works
just fine:

mysql> -- Unordered query
mysql> select name, date_format(birthday, '%d-%b-%Y') from people;
+---------+-----------------------------------+
| name | date_format(birthday, '%d-%b-%Y') |
+---------+-----------------------------------+
| Matthew | 20-Nov-1997 |
| David | 06-Jul-1993 |
| Scott | 17-Feb-1992 |
| John | 03-Nov-1997 |
| Susan | 05-Apr-2001 |
| George | 13-Aug-2000 |
| Henry | 09-Jul-1991 |
| Mary | 16-Jul-2000 |
| Thomas | 14-May-1992 |
| Robert | 20-Mar-1994 |
+---------+-----------------------------------+
10 rows in set (0.00 sec)

mysql> -- Ordered query
mysql> select name, date_format(birthday, '%d-%b-%Y') from people order by birthday;
+---------+-----------------------------------+
| name | date_format(birthday, '%d-%b-%Y') |
+---------+-----------------------------------+
| Henry | 09-Jul-1991 |
| Scott | 17-Feb-1992 |
| Thomas | 14-May-1992 |
| David | 06-Jul-1993 |
| Robert | 20-Mar-1994 |
| John | 03-Nov-1997 |
| Matthew | 20-Nov-1997 |
| Mary | 16-Jul-2000 |
| George | 13-Aug-2000 |
| Susan | 05-Apr-2001 |
+---------+-----------------------------------+
10 rows in set (0.01 sec)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Jul 17 '05 #7