SQL query between two dates using regex

Question

I'm doing a sql query to extract orders from date to date. I think I need to use LIKE because dates are strings and has days, but I have to consider only year and month, for example: order date -> 2019-01-01 regex -> LIKE '%2019-01-%' (all orders in done in january) and then I have to to the same thing for another date (let's say '%2019-03-%'). After that I use the two dates to extract time with BETWEEN. The problem is that I don't know how to put these two things together.

Tip: Use proper date data type, and life will become easier. — jarlh
Show us some sample table data and the expected result - all as formatted text (not images.), also show us your current query attempt. minimal reproducible example — jarlh
Yea don't use strings, should be Date, DateTime or DateTime2. If you use strings you will be causing issues with your database as you won't be able to seek to data, so instead will have to scan the whole table every time, which is not good. Talking from a SQL Server point of view but presume other databases will work the same. As having an index on the Date column would make for much better and faster SQL Queries — Andrew

Popeye Popeye · Accepted Answer · 2021-01-15T08:48:01

You can use the substring and then do comparison as follows:

select * from orders
 where substring(date, 1, 7) between '2019-01' and '2019-03'

Or you can convert your date column into date and then compare it with dates.

SQL query between two dates using regex

1 Answers