Reformat Date and Time from DB

[neophyte]

I've got a date column and a time column in a mySQL table. These columns are formatted 'date' and 'time' respectively, so when the information gets added from a form, the result is, of course: '2005-05-04' and '03:45:03' (for example).

That's fine, as the information is accurate, but I've got to display this information on a page, and don't like the '2005-05-04' format.

I want to reformat this information to display 5/4/2005 or May 4, 2005. Same thing for the time - 3:45 PM rather than '03:45:03'.

Is there built-in function that would handle this kind of date and time conversion for page display, or do I need to write one?

Thanks to all in advance,
Neophyte

[electricocean]

you can use the date() function:

print date ( 'n/j/y' );

the letters are how u want the date to look

go here:
[php.net...]

It tells you all of them

electricocean

[neophyte]

electricocean:

Thanks for the reply, but I think you've misunderstood:

I'm drawing this info from a DB. When I draw it from the DB into a variable (like $date) it's in the default "date" format for that DB column, so when I draw it into a $date variable, it looks like this: 2005-05-07.

Now, when I display $date on a page, I want it formatted like: May 7, 2005. That's my issue. Same with the Time.

I initially set my DB "date" column to be just text and used date('m/j/y') to put the date (already in my desired format) into the DB. But then I thought maybe that wasn't good practice. So, now I'm trying it the other way around: Date is entered into the DB in normal mySQL date format, but when I display it on a page, I want it to be shown in a more typical month, day, year format (May 7, 2005).

Is there a way to do this, or should I just go back to inserting the date into the DB in the format that I want it displayed on a page?

Neophyte

[chadmg]

Check out the MySQL Date Functions:
[dev.mysql.com...]

This is most likely what you want:
SELECT DATE_FORMAT(date_field, '%M %e, %Y')

You also may want to look into combining your date and time fields. I add times into my db as the seconds since the Unix Epoch. It's the number returned from the php function time(). Then I convert it in either MySQL or PHP to the date format I want.

[neophyte]

chadMG -

Thanks for your clarification. Now I'm beginning to understand. One follow-on if you don't mind.

My current query looks like this:

$query = ('SELECT name, city, country, rating, comment, date, time FROM guest ORDER BY id DESC');

How would I reformat the date while retaining one record set? Like this:

$query = ('SELECT name, city, country, rating, comment, DATE_FORMAT(date, '%M %e, %Y'), time FROM guest ORDER BY id DESC');

?

I'm also investigating the issue of dropping both date and time into the same DB field as recommended.

Appreciate all your input.

Neophyte