Readers like you help support MUO. When you make a purchase using links on our site, we may earn an affiliate commission. Read More.
How to select MySQL data between 2 different dates? I have inserted date in the format dd-mm-yyyy. Here is the query I'm using:
SELECT * FROM `entries` WHERE `date` BETWEEN '01-04-2014' AND '30-04-2014'
This table 'entries' contain id, date, particulars, debit, credit and link. I have retrieved the last six months using PHP because I want to know the total of debit and credit for each month. But whenever I run this query, it returns the total of all debit entries and credit entries whether they belong to the specified date range or not.
2014-06-25 22:41:36
Doesn't answer the question, but you should define your select statement as well, instead of using the wildcard. That can really speed things up.
2014-06-24 06:36:24
If the date column in your database was created for holding a Date data type, you need to pass the date string in the proper format (CCYY-MM-DD). So modifying your query to SELECT * FROM `entries` WHERE `date` BETWEEN ’2014-04-01? AND ’2014-04-30? should return the results you want.
2014-06-26 05:08:45
You should change the data type of your 'date' column in the table to DATE. By doing so, the database will perform validation on it that will not happen with a VARCHAR. When you are using VARCHAR, anything at all can be stored in there. If you use a DATE data type, it will not accept an entry such as '2013-02-29' or '2014-80-90' or 'this is my data.' but a VARCHAR field will allow it with no complaints.All database fields should always be the most applicable data type for what it will contain. If it is a date, time, date/time, numeric, boolean, BLOB, or alphanumeric field, there are specific data types that should be used. They allow validation to be performed on them by the database to catch errors that may be made by the coder, offer performance benefits, and, in some cases, save space in the database by allowing it to be stored in a more compact form.