SQL selecting data between particular date and additional field filter

I have the a sql table payments with the following columns:

id|name|amount|reference_number|payment_date|status

And here is some values

1|John Doe|10.00|123456G|2019-02-21 08:00:21|confirmed
2|John Doe|10.00|FHFHgJJ|2019-02-21 10:05:50|pending
3|John Doe|10.00|57GHHFG|2019-02-22 12:10:32|pending
4|John Doe|10.00|GHTYNHJ|2019-02-22 09:52:26|confirmed
5|John Doe|10.00|123G456|2019-02-23 12:22:45|confirmed
6|John Doe|10.00|J123456|2019-02-23 13:00:21|pending

Now I need to fetch select the data based on payment_date and status where sometimes the payment_date can be a range or the same date.

Scenario 1:

SELECT * 
FROM payments 
WHERE payment_date >= '2019-02-21' AND payment_date <= '2019-02-21' AND status='confirmed' 
ORDER BY id DESC

Should return:

1|John Doe|10.00|123456G|2019-02-21 08:00:21|confirmed

Screnario 2:

SELECT * 
FROM payments 
WHERE payment_date >= '2019-02-21' AND payment_date <= '2019-02-23' AND status='confirmed' 
ORDER BY id DESC

Should return:

1|John Doe|10.00|123456G|2019-02-21 08:00:21|confirmed
4|John Doe|10.00|GHTYNHJ|2019-02-22 09:52:26|confirmed
5|John Doe|10.00|123G456|2019-02-23 12:22:45|confirmed

Any work around this?

Answer

As @strawberry suggested use time as well with date to fetch the data

SELECT * 
FROM payments 
WHERE payment_date >= '2019-02-21 00:00:00' AND payment_date <= '2019-02-21 
23:59:59' AND status='confirmed' 
ORDER BY id DESC

Advertisement

Answer