Convert strftime in SQLite request to MySQL

mysql sql sqlite strftime
Paul picture Paul · Jun 23, 2014 · Viewed 10.7k times · Source

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.

Answer

Joachim Isaksson picture Joachim Isaksson · Jun 23, 2014

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') = .......

Related questions

How do I UPDATE a row in a table or INSERT it if it doesn't exist?

I have the following table of counters: CREATE TABLE cache ( key text PRIMARY KEY, generation int ); I would like to increment one of the counters, or set it to zero if the corresponding row doesn't exist yet. Is there a …

Read More
SQLite INSERT - ON DUPLICATE KEY UPDATE (UPSERT)

MySQL has something like this: INSERT INTO visits (ip, hits) VALUES ('127.0.0.1', 1) ON DUPLICATE KEY UPDATE hits = hits + 1; As far as I know this feature doesn't exist in SQLite, what I want to know is if there is any …

Read More
SQLite - ORDER BY RAND()

In MySQL I can use the RAND() function, is there any alternative in SQLite 3?

Read More