SQL date range query

[chambers9661]

Hi i have the following code

Quote

$sql= "SELECT * FROM income WHERE month(date) between '04' and '12' and year(date) between 2020 and 2020";
 

This obviously selects all the information from April 2020 to December 2020

 

How do i change this to add a date as well for example if i wanted to display all the information from April 6th 2020 to December 5th 2020 ?

 

[maxxd]

SELECT *
FROM income
WHERE `date` BETWEEN '2020-04-06 00:00:00' AND '2020-12-05 23:59:59';

 

[StevenOliver]

maxxd, that's a nice way. I like how there is no overlap. Another way, (depending how the mysql table is set up), might be:
select * from my_table where dateInfo >= 2020-01-01 and dateInfo <= '2020-07-19';

 

Edited July 20, 2020 by StevenOliver

[chambers9661]

thanks guys i will try these

[Barand]

7 hours ago, StevenOliver said:

select * from my_table where dateInfo >= 2020-01-01 and dateInfo <= '2020-07-19';

A couple of caveats for the record...

1 ) 2020-01-01, without the quotes, evaluates to 2018 (2020 minus 1 minus 1) but I'm sure it was just a typo in this instance.

2 ) if dateinfo is a datetime type then you need to make sure you use only its date portion when comparing against '2020-07-19' (which is actually '2020-07-19 00:00:00').
If datainfo were to contain '2020-07-19 01:00:00' then it would be outside the range if you don't compare DATE(dateinfo)