I have the below table structure.
ID | FromDate | ToDate
1 | 2020-01-02 | 2020-06-01
2 | 2020-08-01 | 2020-12-01
3 | 2020-01-02 | 2020-11-28
4 | 2020-04-01 | 2020-05-28
When I pass 2 input parameters named fromDate and toDate, it should select the relevant records.
E.g. If FromDate = 2020-01-01 and ToDate = 2020-06-01, it should return records with IDs 1 and 4 since those two records are between the dates as the parameters supplied.
I am using the below method at the moment and I feel like it's wasting a lot of resources.
AND ToDate IN (select * from
(select adddate('2000-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6
union select 7 union select 8 union select 9) t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2021-01-02 00:00:00' and '2022-03-02 00:00:00') ORDER BY name ASC,lt.ID ASC<code>
Is there any other way to achieve this task?
select * from table_name where fromDate>'2020-xx-xx' AND ToDate<'2020-xx-xx';
this query format can get records between insertion dates