MySQL Bugs: #6310: str_to_date( ) does not work all the time

Bug #6310	str_to_date( ) does not work all the time
Submitted:	28 Oct 2004 23:22	Modified:	29 Oct 2004 12:25
Reporter:	Christian Berlioz	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: User-defined functions ( UDF )	Severity:	S3 (Non-critical)
Version:	4.1.7	OS:	Windows (XP professional)
Assigned to:		CPU Architecture:	Any

Description:
On a select statement a table has a field named lastdate C(8) and values are stored as 10/31/04 for oct 31st 2004.  Using str_to_date to convert to date.

There is a problem when operating this char() fields against strings in which all field and strings have been converted using str_to_date() function.  It only works if used on the field name but not in the strings, specially in a between statement.

Any ideas? it should work for both the "lastdate" field name and for the strings provided as slashed dates?

Regards.

How to repeat:
works when:
Select lastdate from table where field=var and str_to_date(lastdate,'%m/%d/%y') between '2004-10-01' and '2004-11-04'  ;

does not work when: 
Select lastdate from table where field=var and str_to_date(lastdate,'%m/%d/%y') between str_to_date('10/01/04','%m/%d/%y') and str_to_date('11/04/04','%m/%d/%y') ... ;

Hi,

Thank you for the report, but I can't repeat it.

mysql> create table t1(
    -> lastdate char(8));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1 values ('10/31/04');
Query OK, 1 row affected (0.00 sec)

mysql> Select lastdate from t1 where str_to_date(lastdate,'%m/%d/%y') between str_to_date('10/01/04','%m/%d/%y') and
    -> str_to_date('11/04/04','%m/%d/%y');
+----------+
| lastdate |
+----------+
| 10/31/04 |
+----------+
1 row in set (0.00 sec)

hi. i have mysql 5.0.18 and when i use str_to_date function as thus i get weird results:

insert into datetest values (str_to_date('08-02-2006','%d-%m-%y'));

instead of 08-02-2006 i get an amazing 2020-02-08 in my table.
could you help me out please?

kaleem

If I invoke the following sql statement I get an error as shown :
mysql> SELECT STR_TO_DATE ('01-01-2006','%m-%d-%Y');
ERROR 1305 (42000): FUNCTION powersoftdb.STR_TO_DATE does not exist

Apparently its because of the extra space I had between STR_TO_DATE and the opening parenthesis.

For Kaleem

Try using a %Y instead of %y that should fix the problem.