Jump to content
chambers9661

SQL date range query

Recommended Posts

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 ?

 

Share this post


Link to post
Share on other sites
SELECT *
FROM income
WHERE `date` BETWEEN '2020-04-06 00:00:00' AND '2020-12-05 23:59:59';

 

Share this post


Link to post
Share on other sites
Posted (edited)

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 by StevenOliver

Share this post


Link to post
Share on other sites
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)

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.