MySQL BETWEEN operator with SQL patterns

Question

I've tried to google the answer, but found nothing, also neither MySQL BETWEEN page nor MySQL Pattern matching page have any information about it.

So, I'm curious, is it correct to use SQL patterns in BETWEEN operator, like SELECT * FROM table WHERE date BETWEEN '2019-01-01%' AND '2019-02-01%' in case of date pattern is Y-m-d H:i.

It is not recommended to use SQL patterns in =, > and < cases, but nothing about BETWEEN

Gordon Linoff · Accepted Answer · 2019-03-12 15:16:42Z

1

First, there is no like support with between. It just doesn't make sense.

Second, you should not be using string functions on dates. MySQL has lots of really useful string fucntions.

And finally, you can just use regular comparisons:

where date >= '2019-01-01' and date < '2019-02-02'

Ironically, this will work both when the date column is stored (properly) as a date. And also what it is stored as a datetime. And as a string (given the format you specify).

answered Mar 12, 2019 at 15:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Scilef Over a year ago

Exactly, I've wondered about it because of this works with datetime fine. So, it's just drops pattern in BETWEEN statement?

Gordon Linoff Over a year ago

@Scilef . . . They are just characters in a string. And you are forcing type conversions for the comparison. And it might be affected by internationalization settings on your system. And it might prevent index usage. And accessing partitions in a partitioned table. An all-around bad idea.

Collectives™ on Stack Overflow

MySQL BETWEEN operator with SQL patterns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related