chambers9661 Posted July 19, 2020 Share Posted July 19, 2020 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 ? Quote Link to comment https://forums.phpfreaks.com/topic/311136-sql-date-range-query/ Share on other sites More sharing options...
maxxd Posted July 19, 2020 Share Posted July 19, 2020 SELECT * FROM income WHERE `date` BETWEEN '2020-04-06 00:00:00' AND '2020-12-05 23:59:59'; Quote Link to comment https://forums.phpfreaks.com/topic/311136-sql-date-range-query/#findComment-1579838 Share on other sites More sharing options...
StevenOliver Posted July 20, 2020 Share Posted July 20, 2020 (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 July 20, 2020 by StevenOliver Quote Link to comment https://forums.phpfreaks.com/topic/311136-sql-date-range-query/#findComment-1579842 Share on other sites More sharing options...
chambers9661 Posted July 20, 2020 Author Share Posted July 20, 2020 thanks guys i will try these Quote Link to comment https://forums.phpfreaks.com/topic/311136-sql-date-range-query/#findComment-1579846 Share on other sites More sharing options...
Barand Posted July 20, 2020 Share Posted July 20, 2020 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) Quote Link to comment https://forums.phpfreaks.com/topic/311136-sql-date-range-query/#findComment-1579848 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.