CODEWITHSUNDEEP
mysqlsqldatabase
Sridhar Karuppusamy
Sridhar Karuppusamy

Reputation: 422

Get Data between two different Date with MYSQL

Table : (Employee)

Data

I need to get Data Between two Days I'm using the following query to get data between two dates

SELECT * FROM employee WHERE Price ((DATE('2020-01-01') BETWEEN DATE(start_date) AND DATE(end_date)) OR (DATE('2020-01-03') BETWEEN DATE(start_date) AND DATE(end_date)) OR (DATE(start_date) >= DATE('2020-07-03')) AND DATE(end_date) <= DATE('2020-07-03'));

I Got the following Result :

REsult

But I Expect the Following result

excpect

So, How can i get my expected result data in mysql

Upvotes: 0

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Your query seems to have nothing to do with your sample data.

Based on your data, you seem to need to generate the dates. You can do this using a recursive CTE and then join:

with recursive dates as (
      select date('2020-01-01') as dte
      union all
      select dte + interval 1 day
      from dates
      where dte < date('2020-01-04')
     )
select d.dte, e.end_date, e.employeename
from employee e join
     dates d
     on d.dte between e.start_date and e.end_date

Here is a db<>fiddle.

Upvotes: 1

Related Questions