Problem formatting date a time

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Swede78
Forum Contributor
Posts: 198
Joined: Wed Mar 12, 2003 12:52 pm
Location: IL

Problem formatting date a time

Post by Swede78 »

I posted a thread similar to this a few days ago, and I appreciate the replies I received. However, I'm still not able to format a date field how I want.

I'm inserting dates into mysql with the format YYYY-MM-DD into a "date" field. I'm not using timestamps.

I browsed the mysql date and time functions page (http://www.mysql.com/doc/en/Date_and_ti ... ml#IDX1305)and came up with this non-working code:

$query = "SELECT DATE_FORMAT(MyDate, '%b %e, %Y'), TIME_FORMAT(MyTime, '%l:%i %p') FROM mytable;

This didn't work, so I though maybe it's because it's not in a timestamp format, so I tried this:

$query = "SELECT DATE_FORMAT(UNIX_TIMESTAMP(MyDate), '%b %e, %Y'), TIME_FORMAT(UNIX_TIMESTAMP(MyTime), '%l:%i %p') FROM mytable;


That doesn't work either. In my first post, I questioned whether or not dates were able to be formatted from a non-timestamp value. I really didn't get an answer to that. So, can someone please tell me if I'm wasting my time trying to format a mysql "date" field (YYYY-MM-DD).
Swede78
Forum Contributor
Posts: 198
Joined: Wed Mar 12, 2003 12:52 pm
Location: IL

Re: Problem formatting date a time

Post by Swede78 »

Swede78 wrote: $query = "SELECT DATE_FORMAT(MyDate, '%b %e, %Y'), TIME_FORMAT(MyTime, '%l:%i %p') FROM mytable;

This didn't work...
I just figured it out... of course right after I posted this. I can't say why this worked, but it does. I added "as MyDate" and "as MyTime" to the statement as follows:

$query = "SELECT DATE_FORMAT(MyDate, '%b %e, %Y') as MyDate, TIME_FORMAT(MyTime, '%l:%i %p') as MyTime FROM mytable;

Answered my own question... but if someone wants to elaborate on why this works, be my guest. I can't find anything that shows that this is how you're supposed to do it. Lucky guess.
Jim
Forum Contributor
Posts: 238
Joined: Fri Apr 19, 2002 5:26 am
Location: Near Austin, Texas

Post by Jim »

You have to define the information as variables if you want to use it. Otherwise it's useless information :)
Swede78
Forum Contributor
Posts: 198
Joined: Wed Mar 12, 2003 12:52 pm
Location: IL

Post by Swede78 »

Why wouldn't they show that in the examples at mysql.com?
Post Reply