mysql DATE_FORMAT STR_TO_DATE date subquery

Question

Here is the RECORDS MySQL TABLE:

ID - BIGINT(20)
DATE - DATE

Records:

ID Date
1  2014-03-01
2  2014-03-02
3  2014-04-01
4  2014-04-02

Here is the first Query:

SELECT id, DATE_FORMAT(date,'%d/%m/%Y') AS date FROM records
WHERE
date >= STR_TO_DATE('01/04/2014','%d/%m/%Y') AND
date <= STR_TO_DATE('30/04/2014','%d/%m/%Y')

Everything is OKAY, I get the result:

3,2014-04-01
4,2014-04-02

But, when I try to get the date range with a subquery like this:

SELECT * 
FROM (SELECT id, DATE_FORMAT(date,'%d/%m/%Y') AS date 
      FROM records) AS TEST
WHERE
date >= STR_TO_DATE('01/04/2014','%d/%m/%Y') AND
date <= STR_TO_DATE('30/04/2014','%d/%m/%Y')

I get an empty result set (i.e. zero rows).

Where is my mistake?

because of your subquery and the alias name is date in the outer query a string and not a date anymore. — VMai, May 10 '14 at 11:40
I tried with:SELECT * FROM (SELECT id, DATE_FORMAT(date,'%d/%m/%Y') AS date FROM records) AS TEST WHERE TEST.date >= STR_TO_DATE('01/04/2014','%d/%m/%Y') AND TEST.date <= STR_TO_DATE('30/04/2014','%d/%m/%Y') I get again empty result set! — eXtreme, May 10 '14 at 11:42
Why not just provide the date in the correct format to begin with? — Strawberry, May 10 '14 at 13:51

jackfrankland · Answer 1 · 2014-05-10T12:22:18.587

As far as I understand it, you wish to just return the date in %d/%m/%Y format. The results I'm getting for your first query in your question do actually yield this converted date, are you sure that's the query you used?

Either way, if this is what you want, I believe you should change the query to something like this:

SELECT id, DATE_FORMAT(date,'%d/%m/%Y') AS datestring FROM records
WHERE
date >= STR_TO_DATE('01/04/2014','%d/%m/%Y') AND
date <= STR_TO_DATE('30/04/2014','%d/%m/%Y')

Even though the date column is used in the comparison rather than the alias, i think it clarifies things.

Here's a SQLFiddle showing the results, as you can see your first query is returning the desired result.

That's the way I would go too: simple without the unnecessary subquery (+1). — VMai, May 10 '14 at 13:26

echo_Me · Answer 2 · 2014-05-10T11:55:12.030

0

you have to include your where clause in TEST.

  SELECT * 
 FROM (SELECT id, DATE_FORMAT(date,'%d/%m/%Y') AS date 
  FROM records
 WHERE
 date >= STR_TO_DATE('01/04/2014','%d/%m/%Y') AND
 date <= STR_TO_DATE('30/04/2014','%d/%m/%Y') 
   ) AS TEST

But i dont know what the reason to use subquery here ?

EDIT:

try this

 select * FROM(
 SELECT id, CASE WHEN date BETWEEN STR_TO_DATE('01/04/2014','%d/%m/%Y')
                           AND STR_TO_DATE('30/04/2014','%d/%m/%Y') 
                 THEN DATE_FORMAT(date,'%d/%m/%Y') 
            END AS date 
 FROM records
) as TEST
WHERE //////  some other conditions

edited May 10 '14 at 11:55

answered May 10 '14 at 11:43

echo_Me

35,836
5
52
76

I don't want it to be in the subquery, is there any other solution? – eXtreme May 10 '14 at 11:45
I really need something like this: SELECT * FROM (SELECT id, DATE_FORMAT(date,'%d/%m/%Y') AS date FROM records) AS TEST WHERE SOMETHING HERE – eXtreme May 10 '14 at 11:53
SELECT * FROM (SELECT id, date FROM records) AS TEST WHERE date >= STR_TO_DATE('01/04/2014','%d/%m/%Y') AND date <= STR_TO_DATE('30/04/2014','%d/%m/%Y') Everything is working like this, but I want to convert displayed date as %d%m%Y before display it without using PHP or any other language – eXtreme May 10 '14 at 11:59

score 0 · Answer 3 · answered May 10 '14 at 11:55

DATE, DATETIME, and TIMESTAMP are naturally ordered data types. That is, they do what you expect when you use inequalities and the BETWEEN statement.

Their default text representation -- 2014-05-07 11:59:56 -- also happens to be naturally ordered when sorted as text.

But when you do DATE_FORMAT(date,'%d/%m/%Y') you convert the naturally ordered representation to one that is not naturally ordered.

    03/15/2014  will sort before
    03/16/2013

even though it is not, in fact, before.

If you want to do date arithmetic, you need to keep the dates in a naturally ordered form until they're actually displayed. Most people don't use DATE_FORMAT() in SQL to convert dates to to a displayable format; instead they use a Java or PHP program to do the display. That way the dates remain naturally ordered.

In your question your outer query can't tell that it's doing BETWEEN on dates. You've told it to do BETWEEN on strings, that don't happen to be naturally ordered.

You said in your comment to @echo_Me's question you don't want BETWEEN in the subquery. You either must put the DATE_FORMAT in the outer query or BETWEEN in the subquery.

mysql DATE_FORMAT STR_TO_DATE date subquery

3 Answers3