What code should I use, instead of running a query that uses DATA_FORMAT()?

Question

I am converting Drupal 7 code to Drupal 8. In Drupal 7, I am using db_query(); in Drupal 8, I am using the following code.

\Drupal::database()->select('node_field_data', 'spmw')
  ->fields('spmw', ['id', 'created' => 'DATE_FORMAT(created, "%H:%i")'])
  ->execute()
  ->fetchObject();

When I run it, it causes the following error.

1054 Unknown column 'spmw.DATE_FORMATcreatedHi' in 'field list': SELECT spmw.id AS id, spmw.DATE_FORMATcreatedHi

What code should I instead use to avoid that error?

Answer 1

You can try \Drupal::database()->select->addExpression().

For you, below code might be useful.

\Drupal::database()
  ->select('node_field_data', 'spmw')
  ->fields('spmw', ['id'])
  ->addExpression("DATE_FORMAT(created,'%H:%i')", "created")
  ->execute()
  ->fetchObject(); 

Answer 2

Although this is an old question, I would like to point out an alternate method. The problem is that Drupal chokes on the parameter of the MySQL DATE_FORMAT function specifying how the date should be formatted. Suppose you have this code:

$SQL = 'SELECT DATE_FORMAT(NOW(), "%e %b %Y")';

$query = $db->query($SQL);

$query->execute();

Executing this in Drupal (as of version 8.8.2) will result in the message "The website encountered an unexpected error. Please try again later." and the following entry in the log:

Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42S22]: 
Column not found: 1054 Unknown column '%e %b %Y' in 'field list':
SELECT DATE_FORMAT(NOW(), "%e %b %Y"); Array ( )

To work around that, you can use a bound parameter for the date format. This code will work:

$SQL = 'SELECT DATE_FORMAT(NOW(), :dateFormat)';
$terms = [':dateFormat' => '%e %b %Y'];

$query = $db->query($SQL, $terms);

$query->execute($terms);

$result = $query->fetchAll();

I suspect the % symbols in the DATE_FORMAT are what's causing the problem. Moving those to a bound parameter solves the issue because Drupal always passes those directly to the database as a literal string.

Hoping this may help some future sufferer.