MySQL question

[MarkO]

I have a table with a date field. The field is populated with numerous entries in the format of yyyy-mm-dd. Say there's an entry stored as 2001-02-28. I would like to run a query and have the output read February 28th, 2001. I came across the date_format function and it looks like what I need but what would be the correct syntax to use?

If MySQL can't do this is there a way to covert the output using PHP?

Thanks
Mark

[Sweede]

mysql has no date formating functions (unless i missed it in the manual, but im 99.43% sure of it).
www.php.net/manual/ (http://www.php.net/manual/)

[MarkO]

Originally posted by Sweede:
mysql has no date formating functions (unless i missed it in the manual, but im 99.43% sure of it).
www.php.net/manual/ (http://www.php.net/manual/)

Sweede-

In a round about way you answered my question. I was going through the php manual looking for a way to have php convert the date and I was reading the user added comments and found the mysql query I needed.

In my case, the following query gave me my desired results: SELECT DATE_FORMAT(date, '%M %D, %Y') from schedule;

Very cool!!

Thanks
Mark

[Sweede]

Cool !
hehe, learn something new everyday http://www.linuxnewbie.org/ubb/smile.gif

but as im sure someone else will point out, doing it this way isnt very portabale since postgres or something else might not support it.

here's a quick little ditty (modified from the comments on the php manual)


function datetime($datestr,$timeformat='') {
if($timeformat == ''){ $timeformat = "l, F jS, Y g:i a T";}
list($year,$month,$day,$hour,$minute,$second) = split("([^0-9])",$datestr);
return date($timeformat,mktime($hour,$minute,$second,$mon th,$day,$year));
}

echo datetime("2000-11-16 19:17:59");


in mysql, datetime will leave this..
2000-11-16 19:17:59

the function above will out put.
Thursday, November 16th, 2000 7:17 pm CST

if you want, you can take the datetime() and add your own date() modifier to it.

echo datetime("2000-11-16 19:17:59","Y-m-d");

which will output
2000-11-16
as expected.

[MarkO]

This is what I used to get the complete results I needed.

<?
global $db;
$sql = "SELECT date_format(date, '%M %D, %Y) from schedule where selected = \"y\"
";
$result = @mysql_query($sql, $db) or die("Couldn't excute query");
$date = mysql_fetch_row($result);

echo $date["0"];
?>


Again, Thanks! http://www.linuxnewbie.org/ubb/smile.gif
Mark

[Sweede]

$sql = "SELECT date_format(date, '%M %D, %Y) from schedule where selected = 'y'
";

is the same thing, http://www.linuxnewbie.org/ubb/smile.gif (changed \" to ' )

[klamath]

im sure someone else will point out, doing it this way isnt very portabale since postgres or something else might not support it.


Yeah, Postgres' main date manipulation function is to_char(). You could:

- make a function within the database which would call the right function and return the modified date. Oh wait - MySQL doesn't support functions...
- call date_format() for both databases. With MySQL, just allow the function itself to be called. With Postgres, write a function called date_format() which takes the MySQL args, converts them as necessary, and returns the results of to_char()

------------------
- Klamath
Get my GnuPG Key Here (http://klamath.dyndns.org/mykey.asc)
Looking for an open source project to contribute to? Check out the Better Bulletin Board (http://bbb.sourceforge.net)

[Sweede]

Klamath, the only reason i sugested an alternative is because i knew you would post something about posgres in a subject that has MySQL in it http://www.linuxnewbie.org/ubb/smile.gif