[SOLVED] Drupal db_query and date formats

Here's small issue I came across and didn't find many answers online. When using Drupal's db_query method to execute some SQL that contains dateformats in it the weekday always comes out as a zero.
for example:

db_query("SELECT DATE_FORMAT(from_unixtime(changed), '%Y-%m-%d') lastmod FROM node LIMIT 10");

This will return dates like 2011-09-0. The month day will always be zero. It will also match %s (seconds), %f (microseconds), and %b (abbreviated month name) Don't worry there's a simple solution.



The Explanation
In short this is due to Drupal's placeholder replacement routines. It will look for %d (or the other listed above) and look for an SQL placeholder for a decimal for %d, string for %s and float for %f.

So in the example query above no placeholder are passed, but Drupal expects a decimal. So it replaces %d with '0'.

The Solution
The solution is simple you can use  a double %. Like this:

db_query("SELECT DATE_FORMAT(from_unixtime(changed), '%%Y-%%m-%%d') lastmod FROM node LIMIT 10");

or you can pass the dateformat to db_query as a placeholder, like so:

db_query("SELECT DATE_FORMAT(from_unixtime(changed), %s) lastmod FROM node LIMIT 10", '%Y-%m-%d');

So in this case Drupal will replace %s with the desired dateformat.

Hope this is helpful, it might save you trying to read the Spanish thread on Drupal anyway.