2
select '2011-02-29' BETWEEN '2011-02-01' AND '2011-03-03'‎

this is returning 1. I think between doesn't consider leap year. I want your view on this?

[EDIT]
SELECT DATE( '2010-04-31' ) is returning NULL;
But select str_to_date('2010-04-31', '%Y-%m-%d') is retuning date.

Why?

Thanks Venu

Venu
  • 7,243
  • 4
  • 39
  • 54

4 Answers4

4

you need to cast it to date like:

SELECT DATE('2011-02-29') BETWEEN DATE('2011-02-01') AND DATE('2011-03-03')

from the site :

For best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type. Examples: If you compare a DATETIME to two DATE values, convert the DATE values to DATETIME values. If you use a string constant such as '2001-1-1' in a comparison to a DATE, cast the string to a DATE.

Haim Evgi
  • 123,187
  • 45
  • 217
  • 223
2

Problem is you're comparing strings, not dates. If you try:

select DATE('2011-02-29') you get a NULL...
Marco
  • 56,740
  • 14
  • 129
  • 152
1

You're comparing strings ... you have to cast the values (or at least the first one) to DATE

Use this:

 SELECT DATE('2011-02-29') BETWEEN '2011-02-01' AND '2011-03-03'

This will give you NULL because the date is not real.

 SELECT DATE('2008-02-29') BETWEEN '2008-02-01' AND '2008-03-03'

This will give you 1 (TRUE) because the date is real (leap year)

Aziz
  • 20,065
  • 8
  • 63
  • 69
1

DATE checks the validity while str_to_date does not.

mysql> select str_to_date('2010-02-31', '%Y-%m-%d');
+---------------------------------------+
| str_to_date('2010-02-31', '%Y-%m-%d') |
+---------------------------------------+
| 2010-02-31                            |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select str_to_date('2010-04-31', '%Y-%m-%d');
+---------------------------------------+
| str_to_date('2010-04-31', '%Y-%m-%d') |
+---------------------------------------+
| 2010-04-31                            |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> select date('2010-02-31');
+--------------------+
| date('2010-02-31') |
+--------------------+
| NULL               |
+--------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select date('2010-04-31');
+--------------------+
| date('2010-04-31') |
+--------------------+
| NULL               |
+--------------------+
1 row in set, 1 warning (0.00 sec)

UPDATE according to @Aziz , DATE will check whether a date is real or not. According to my test, seems str_to_date does not check.

James.Xu
  • 8,249
  • 5
  • 25
  • 36
  • SELECT DATE( '2010-04-31' ) is returning NULL; But select str_to_date('2010-04-31', '%Y-%m-%d') is retuning date.. – Venu Nov 10 '11 at 07:51