Why isn’t the WHERE clause in my SQL query restricting the records retrieved?

Chinmay S June 24, 2014
Pinterest Stumbleupon Whatsapp

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.

  1. Guy M
    June 25, 2014 at 10:41 pm

    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.

  2. Bruce E
    June 24, 2014 at 6:36 am

    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.

    • Chinmay S
      June 24, 2014 at 7:36 am

      I have changed the format but didn't change the data type from VARCHAR to DATE but now it's working. Is there any way I can change this format because I have to convert old format to this one and insert into the database and then at the time of retrieving it back, I have to change it back to the old one.

      I am using Datepicker from JQuery UI to enter date and it's format is dd-mm-yyyy. So, I have to use explode() and then take the elements of array and rearrange them and then the same process again for displaying. It could have been much easier if there was a way to change the default format.

    • Bruce E
      June 24, 2014 at 12:38 pm

      JQuery UI provides the means to format the selected date however you want. See the documentation showing how to accomplish it.

      If you don't want to do it like that, you can use STR_TO_DATE() to change the format for input to the database with INSERT INTO `entries` (`date`) VALUES (STR_TO_DATE('01-04-2014' , '%d-%m-%Y')). [Note: This is an abbreviated insert command since I don't know the rest of the columns in your table.] This will convert your dd-mm-ccyy input format to the ISO 8601 date format supported by SQL.

      When retrieving the information, you can use the DATE_FORMAT function to automagically return your date data in dd-mm-ccyy format by using SELECT DATE_FORMAT(`date' , '%d-%m-%Y') FROM `entries` WHERE `date` BETWEEN '2014-04-01' AND '2014-04-30'. Depending on how you are using the returned results, you could also use SELECT *, DATE_FORMAT(`date`) FROM... which will give you the stored ISO 8601 format date column which you can ignore during further processing if you wish as well as the reformatted one or you could explicitly select all of the columns you want instead. In most cases, it is considered good form to explicitly select your columns and only include those that are needed for the process.

      I am also assuming you are not hard-coding dates into your SELECT query. Those will be variables, correct? You can use the STR_TO_DATE() function on those as well.

    • Bruce E
      June 26, 2014 at 5:08 am

      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.