current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Drupal Answers is a question and answer site for Drupal developers and administrators. It's 100% free, no registration required.
up vote 2 down vote favorite

How can I do a MySQL date function in Drupal? I have this now:

$select = db_select('someTable', 'st');
$select->addExpression('DATE_FORMAT(FROM_UNIXTIME(created), "%Y-%m")', 'createdMonth');

$date = $select->execute()->fetchAll();

When I run this I get an exception that says:

"SQLSTATE[42S22]: Column not found: 1054 Unknown column '%Y-%m' in 'field list'"

But if I print the $select object I see it should run

SELECT DATE_FORMAT(FROM_UNIXTIME(created), "%Y-%m") AS createdMonth FROM {someTable} st

And when I copy this to Sequel Pro it just runs.

What am I missing?

share|improve this question
    
Take a look on this it may helps you cgit.drupalcode.org/month/commit/?id=a8b28ea –  Smalution Aug 30 '14 at 11:50

1 Answer 1

up vote 1 down vote accepted

You're a 'victim' of MySQL's ANSI_QUOTES SQL mode:

Treat “"” as an identifier quote character (like the “`” quote character) and not as a string quote character. You can still use “`” to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotation marks to quote literal strings, because it is interpreted as an identifier.

Emphasis mine.

That explains why MySQL is trying to find a column called '%y-%m' rather than interpreting it as a string.

So you need to switch your SQL mode to something else, or (more reasonably), just use single quotes for literals:

$select->addExpression("DATE_FORMAT(FROM_UNIXTIME(created), '%Y-%m')", 'createdMonth');

Many other DBMSs don't accept double quotes for string literals, so using single quotes is a good habit to get into anyway.

share|improve this answer
    
I think the Drupal coding standards suggest to use the double quotes for SQL strings exactly to avoid this problem. It has been a while since I was approving the CVS applications to become maintainer, so the coding standards could be changed, in the while. :D –  kiamlaluno♦ Aug 31 '14 at 2:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.