Dates

[Mosher]

Hi all, I have a date entered into a mysql database in the format:
yyyy-mm-dd. I want to retrieve the date and display it in a different format
using php. For example, if the mysql date entered is: 2005-03-08, I would
like to display it on a webpage as:

March 8th, 2005

Any ideas? I have read up on the php date() function but I only know how to
get the current date and not how to format/display existing dates in
different formats.

Thanks in advance,

Mosher

[Michael Fesser]

.oO(Mosher)

Hi all, I have a date entered into a mysql database in the format:
yyyy-mm-dd. I want to retrieve the date and display it in a different format
using php. For example, if the mysql date entered is: 2005-03-08, I would
like to display it on a webpage as:

March 8th, 2005

Any ideas?

Have a look at MySQL's DATE_FORMAT() function.

Micha

[John Bokma]

Mosher wrote:

Hi all, I have a date entered into a mysql database in the format:
yyyy-mm-dd. I want to retrieve the date and display it in a different
format using php. For example, if the mysql date entered is:
2005-03-08, I would like to display it on a webpage as:

March 8th, 2005

Any ideas? I have read up on the php date()

Don't. Most databases support several date formatting functions. Every time
you use a database ask yourself: can the database do this? I have seen a
lot of programs that are cluttered with code that just repeats
functionality that is present in the database.

If you are serious at using MySQL take some time to carefully read through
all the functions it supports. Make notes regarding the handy ones, and the
ones you *think* you will never use ;-)

--
John MexIT: http://johnbokma.com/mexit/
personal page: http://johnbokma.com/
Experienced programmer available: http://castleamber.com/
Happy Customers: http://castleamber.com/testimonials.html

[Mosher]

Guys, I did a little more investigating and found my solution using the
"explode" function in PHP. My solution looks something like this:

$row_date = explode("-", $row[event_date]);
$date = date('F jS, Y',
mktime(0,0,0,$row_date[1],$row_date[2],$row_date[0]));

Thanks for your help!

Mosher

"Mosher" <mo***********@yahoo.com> wrote in message
news:ZY********************@comcast.com...

Hi all, I have a date entered into a mysql database in the format:
yyyy-mm-dd. I want to retrieve the date and display it in a different
format using php. For example, if the mysql date entered is: 2005-03-08, I
would like to display it on a webpage as:

March 8th, 2005

Any ideas? I have read up on the php date() function but I only know how
to get the current date and not how to format/display existing dates in
different formats.

Thanks in advance,

Mosher

[Michael Fesser]

.oO(Mosher)

Guys, I did a little more investigating and found my solution using the
"explode" function in PHP. My solution looks something like this:
It's ugly.
$row_date = explode("-", $row[event_date]);
$date = date('F jS, Y',
mktime(0,0,0,$row_date[1],$row_date[2],$row_date[0]));

What's wrong with letting the database do the work for you? You just
have to add a call to DATE_FORMAT() to your query, that's all. No need
for explode(), mktime() and date().

Micha

[John Bokma]

Mosher wrote:

Guys, I did a little more investigating
I doubt it.
and found my solution using
the "explode" function in PHP.
Which is the worst possible solution.
My solution looks something like this:

$row_date = explode("-", $row[event_date]);
$date = date('F jS, Y',
mktime(0,0,0,$row_date[1],$row_date[2],$row_date[0]));

3 lines of code which can be done with just one MySQL function...

--
John MexIT: http://johnbokma.com/mexit/
personal page: http://johnbokma.com/
Experienced programmer available: http://castleamber.com/
Happy Customers: http://castleamber.com/testimonials.html

[Jan Pieter Kunst]

Mosher wrote:

Hi all, I have a date entered into a mysql database in the format:
yyyy-mm-dd. I want to retrieve the date and display it in a different format
using php. For example, if the mysql date entered is: 2005-03-08, I would
like to display it on a webpage as:

March 8th, 2005

Any ideas? I have read up on the php date() function but I only know how to
get the current date and not how to format/display existing dates in
different formats.

SELECT DATE_FORMAT(your_date_field, '%M %D, %Y') FROM your_table

See <http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html>.

HTH,
JP

--
Sorry, <de*****@cauce.org> is a spam trap.
Real e-mail address unavailable. 5000+ spams per month.

[Jean-Baptiste Nizet]

If you lived in a country where there are 3 official languages, and
thus 3 different ways to display a date, you'd understand that this
solution is not a good one. It's not the database's job to handle the
presentation aspects of the application. You should'nt mix presentation
code with data access code.
The best way to do this is to transform the date into a unix timestamp,
and then use the function strftime
(http://fr3.php.net/manual/en/function.strftime.php).
To transform the MySQL date into a timestamp, use the php funtion
strtotime, or the MySQL function UNIX_TIMESTAMP as described here:
http://fr.php.net/manual/en/function.date.php

JB.

[Michael Fesser]

.oO(Jean-Baptiste Nizet)

If you lived in a country where there are 3 official languages, and
thus 3 different ways to display a date, you'd understand that this
solution is not a good one.
No problem with that. Set the format string in DATE_FORMAT() according
to the reqested language.
It's not the database's job to handle the
presentation aspects of the application. You should'nt mix presentation
code with data access code.
Quite true, but I don't consider that an issue in this case. Assume a
database containing texts in multiple languages. The application simply
calls "get me this and that text". The DB code has to know the requested
language in order to return the correct variant. And if the code is able
to return text in different languages it should also be able to return a
date in different formats.
The best way to do this is to transform the date into a unix timestamp,
and then use the function strftime
(http://fr3.php.net/manual/en/function.strftime.php).

I never consider Unix timestamps the best solution, simply because of
their range restriction. While it will work in many cases, it will fail
in others.

Micha

[John Dunlop]

Mosher wrote:

March 8th, 2005

I prefer '8 March 2005'. The comma is then unnecessary
because the name of the month separates the numerals, and
the expression runs in order of significance. Also, the
ordinal suffix is superfluous; 'March 8th, 2005' says
nothing that 'March 8, 2005' doesn't.

--
Jock

[Jean-Baptiste Nizet]

And where are you going to perform the reverse operation: parse a date
entered by a user to put it in the database? Are you goinf to use the
MySQL function STR_TO_DATE()? How do you perform validation and return
meaningful error messages then? And if you don't use STR_TO_DATE, don't
you find this asymmetricity a bit strange?
And if you have to display a date which doesn't come from the database,
how are you gonna use the MySQL function? You'll duplicate date
formatting code?
And if you decide to use Oracle, DB2 or PostgreSQL rather than MySQL,
you're gonna change all your presentation code?

[Michael Fesser]

.oO(Jean-Baptiste Nizet)

And where are you going to perform the reverse operation: parse a date
entered by a user to put it in the database? Are you goinf to use the
MySQL function STR_TO_DATE()? How do you perform validation and return
meaningful error messages then?
PHP
And if you don't use STR_TO_DATE, don't
you find this asymmetricity a bit strange?
No. I always use the tool that I think is most appropriate and efficient
to solve a particular problem.
And if you have to display a date which doesn't come from the database,
how are you gonna use the MySQL function? You'll duplicate date
formatting code?
For other language-dependent output 'gettext' is an option.
And if you decide to use Oracle, DB2 or PostgreSQL rather than MySQL,
you're gonna change all your presentation code?

How often do you change the used database? Of course you can avoid many
problems with using a full blown DB abstraction layer, but at what cost?
I prefer to make the best use of the features offered by the DBMS. Or in
other words: I prefer efficiency and performance over abstraction.

Micha

[Ewoud Dronkert]

On Wed, 9 Mar 2005 14:30:46 -0600, Mosher wrote:

Hi all, I have a date

Congratulations.
--
Firefox Web Browser - Rediscover the web - http://getffox.com/
Thunderbird E-mail and Newsgroups - http://gettbird.com/