MySQL date & time format funcs not working

[Anon]

I just switched hosts and on the new host when the date and time fields are pulled from the database they are not formatted how they are supposed to be ( they worked fine on the old host ). Here's the query string..

$SQL_Query = "SELECT *, DATE_FORMAT( date, 'W - m/d/y' ) as date, TIME_FORMAT( time, '[l:i p]' ) as time FROM news WHERE TO_DAYS(NOW()) - TO_DAYS( date ) < $days ORDER BY id DESC";

Everything look right? I'd hope since it works fine on the old host, and the data is correct in the database, but why wouldn't it work on the new one? The date is displayed as

W - m/d/y [l:i p]

which is obviously just the exact text in the query. Thanks for any help..

[Anon]

MySQL versions earlier than 3.23.0 were allowed to get away with the code you were using. Versions as of 3.23.0 require a '%' character to precede the format codes. So, your new server is using a newer version of MySQL. Try this instead:

$SQL_Query = "SELECT *, DATE_FORMAT( date, '%W - %m/%d/%y' ) as date, TIME_FORMAT( time, '[l:i p]' ) as time FROM news WHERE TO_DAYS(NOW()) - TO_DAYS( date ) < $days ORDER BY id DESC"

I believe the TIME_FORMAT is still workin for ya, though, correct?

please lemme know if this helps,
good luck 🙂

Blake
[e-mail]

[Anon]

That did it, I figured it had something to do with the version of MySQL. Thanks a bunch, and I did have to put the % signs before the time formatting elements as well.

Thanks again,
Justin