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?

[Tom Thackrey]

On 11-Nov-2003, [email]adrook@yahoo.com[/email] (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
[url]www.creative-light.com[/url]
tom (at) creative (dash) light (dot) com
do NOT send email to [email]jamesbutler@willglen.net[/email] (it's reserved for spammers)

[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
--

[Michael Fuhr]

[email]adrook@yahoo.com[/email] (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
[url]http://www.fuhr.org/~mfuhr/[/url]

[Tony Marston]

[email]adrook@yahoo.com[/email] (Kit) wrote in message news:<e756f30f.0311112315.42cda4eb@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 [url]http://www.mysql.com/doc/en/Date_and_time_functions.html[/url]
particularly the bit about the DATE_FORMAT function.

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

Tony Marston
[url]http://www.tonymarston.net/[/url]

[Markus Ernst]

"Michael Fuhr" <mfuhr@fuhr.org> schrieb im Newsbeitrag
news:3fb1f2fd_2@omega.dimensional.com...

> [email]adrook@yahoo.com[/email] (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

[Michael Fuhr]

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

> "Michael Fuhr" <mfuhr@fuhr.org> schrieb im Newsbeitrag
> news:3fb1f2fd_2@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
[url]http://www.fuhr.org/~mfuhr/[/url]