Convert strftime in SQLite request to MySQL

Question

I converted the SQLite line

WHERE strftime('%d%m', orders.created_at) = .......

directly to a MySQL monster:

WHERE CONCAT(CAST(DAY(orders.created_at) AS CHAR), LPAD(CAST(MONTH(orders.created_at) AS CHAR), 2, '0')) = .........

Please, help me to rewrite it to a shorter one.

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format — Joachim Isaksson, Jun 23 '14 at 08:07

score 16 · Accepted Answer · answered Jun 23 '14 at 08:18

16

STRFTIME() in SQLite is similar to DATE_FORMAT() in MySQL with reversed parameters.

Since %d and %m map to the same thing in both, your expression can simply be written as;

WHERE DATE_FORMAT(orders.created_at, '%d%m') = .......

answered Jun 23 '14 at 08:18

Joachim Isaksson

When I do this, I get following error: DatabaseError: (1054, "Unknown column 'LeadListEntry.created' in 'field list'"). Here LeadListEntry is table name and created is datetime field? – Sid Jun 12 '17 at 11:41

1 Answers1