Question 190747 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL SELECT DATE Between

I started to SET my dates to (%d. %M %Y).

But now I cant find them with:

SELECT value FROM _data WHERE dates >= "01. October 2012" AND <= "01. December 2016"

The default DATE_FORMAT was YYYY-MM-DD hh:mm:ss.000 before the UPDATE comand.

Can someone explain whats the problem here?

I tried the it with LIKE '%<date>%' but it changed nothing.

Tags:

3

Answers


  1. 0

    Use yyyy-MM-dd format which is independent of date settings:

    select value from _data where dates between '2012-10-01' and '2016-12-01';

    EDIT: A date is a date and %d %M %Y is just a display format. Underlying data is still a date. You shouldn’t fall into a mistake like storing them as string.

    Here is a DBFiddle demo.

    Login or Signup to reply.
  2. 0

    Use STR_TO_DATE to convert dates to a proper format.

    SELECT value FROM _data
WHERE dates >= STR_TO_DATE('01. October 2012', '%d. %M %Y')
AND dates <= STR_TO_DATE('01. December 2016', '%d. %M %Y');
    Login or Signup to reply.
  3. 0
    1. "01. October 2012" is a column name in SQL, because of the double quotes. MySQL allows these for strings, too, but you should always use single quotes for string literals.
    2. So, '01. October 2012' is a string. It starts with the characters ‘0’ then ‘1’ then ‘.’ then ‘ ‘ then ‘O’. This comes after '01. April 2023' for instance, because ‘A’ < ‘O’. This is not at all what you want. You don’t want to work with strings. You want to deal with dates.
    3. A date literal in SQL starts with the word DATE followed by a string in the format ‘yyyy-mmdd’.
    4. Often we don’t only deal with dates, but also with datetimes / timestamps. To play safe we often use >= startdate and < enddate + 1 for date ranges.
    5. One of your conditions is AND <= "01. December 2016". This is not a condition. You forgot to tell the DBMS which column shall be less or equal the string literal.
    6. Of course, dates should be stored as DATE, not as strings. If your table column is a string type, this should be fixed first.

    The query you finally want should then be:

    SELECT value
FROM _data
WHERE dates >= DATE '2012-10-01'
  AND dates <  DATE '2016-12-02';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search