SQL: Is it possible to % CURDATE % in SELECT

Question

I'm trying to use sql CURDATE(), in format "YYYY-MM-DD", to select items from yesterday but time column is in "YYYY-MM-DD HH-MM-SS" format.

Is it possible to put CURDATE between % %, something like in this example (which obviously doesn't work); SELECT * FROM table WHERE created_at LIKE % CURDATE() - INTERVAL 1 day %

Or if there is no syntax variation which makes use of curdate & also that works, do please recommend another way if there is any in sql bag of tricks.

Are you storing dates as text (e.g. `VARCHAR`)? – Álvaro González Jun 16 '20 at 10:58 — Álvaro González, Jun 16 '20 at 10:58
@ÁlvaroGonzález No. They are stored as datetime. – hmmm___--_--___not_sure Jun 16 '20 at 12:00 — hmmm___--_--___not_sure, Jun 16 '20 at 12:00

Gordon Linoff · Accepted Answer · 2020-06-16T13:33:36.600

1

Don't use string functions as strings! If you want yesterday, you can use:

where created_at >= current_date - interval 1 day and
      created_at < current_date

Plus, this can use an index.

Or, you can simplify this to:

where date(created_at) = current_date - interval 1 day

And this version does not use an index on created_at.

edited Jun 16 '20 at 13:33

answered Jun 16 '20 at 10:55

Gordon Linoff

1,242,037
58
646
786

Note that the second option cannot use an index. – Strawberry Jun 16 '20 at 11:06
Second, more simplified option works and is best suited! Index is not of importance right now. Thanks! – hmmm___--_--___not_sure Jun 16 '20 at 12:03
It seems that MySQL finally supports [function based indexes](https://stackoverflow.com/a/35380963/13508). – Álvaro González Jun 16 '20 at 12:35

SQL: Is it possible to % CURDATE % in SELECT

1 Answers1