Page 1 of 1

Problem formatting date a time

Posted: Thu Jun 05, 2003 11:42 am
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).

Re: Problem formatting date a time

Posted: Thu Jun 05, 2003 11:54 am
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.

Posted: Thu Jun 05, 2003 12:05 pm
by Jim
You have to define the information as variables if you want to use it. Otherwise it's useless information :)

Posted: Thu Jun 05, 2003 12:41 pm
by Swede78
Why wouldn't they show that in the examples at mysql.com?