Bug #33259 str_to_date conversion of date format 'dd-mmm-yy' to 'yyyy-mm-dd'
Submitted: 15 Dec 2007 9:53 Modified: 16 Dec 2007 4:16
Reporter: abhinav mishra Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:MySQL 5.1 OS:Windows
Assigned to: CPU Architecture:Any

[15 Dec 2007 9:53] abhinav mishra
Description:
I dont know if it can be categorized as a bug. 

But the conversion of date in format 'dd-mmm-yy' to 'yyyy-mm-dd' using str_to_date
has issues if year is less than 1960.

select str_to_date('01-JAN-60','%d-%M-%y');

it shud give '1960-01-01'
but the result comes as '2060-01-01'

this happens for any year less than 1970

select str_to_date('01-jan-70','%d-%M-%y');

gives absolutely right answer and so is true for anything above '01-jan-70'

How to repeat:
But the conversion of date in format 'dd-mmm-yy' to 'yyyy-mm-dd' using str_to_date
has issues if year is less than 1960.

select str_to_date('01-JAN-60','%d-%M-%y');

it shud give '1960-01-01'
but the result comes as '2060-01-01'

this happens for any year less than 1970

select str_to_date('01-jan-70','%d-%M-%y');

gives absolutely right answer and so is true for anything above '01-jan-70'
[15 Dec 2007 10:01] abhinav mishra
If it is a bug then I guess its a concern. I figured it out when the 'age' of ppl in my data started figuring -ve (current_date - date_of_birth).
I am sure it must have happened for other too.
[15 Dec 2007 14:35] Peter Laursen
I think this is by design!

http://dev.mysql.com/doc/refman/5.0/en/datetime.html

says

"Dates containing two-digit year values are ambiguous because the century is unknown. MySQL interprets two-digit year values using the following rules: 

Year values in the range 00-69 are converted to 2000-2069. 
Year values in the range 70-99 are converted to 1970-1999."

If you want all years to be 19xx, you can 'code around it' using string functions and convert year, month and day seperately and 'assemble' those to a DATE.  Better option maybe is to use 4 digit year format in the function argument! 

(not an official answer - I am no MySQL person)
[15 Dec 2007 14:53] Peter Laursen
or like this

SELECT str_to_date
  (concat           -- 1st argument for str_to date is assembled from 3 strings
     (SUBSTRING('01-JAN-69',1,7),
      '19',
      SUBSTRING('01-JAN-69',8,2)
   )
  ,'%d-%M-%Y');     -- 2nd argument for str_to date - note UPPERCASE 'Y'

-- returns 1969-01-01
[15 Dec 2007 15:19] abhinav mishra
Ya I did code around the problem.
Since in my data DOB's will always be in 1900's

_______________________________________________________________

ALTER TABLE data_table 
ADD COLUMN `temp_dob1` VARCHAR(10) NOT NULL DEFAULT '' ,
ADD COLUMN `temp_dob2` VARCHAR(10) NOT NULL DEFAULT '',
ADD COLUMN `dob_new` DATE NOT NULL DEFAULT '0000-00-00' ;

update data_table
set temp_dob1=substring_index(date_of_birth,'-',-1)+1900;

update data_table
set temp_dob2=str_to_date(date_of_birth,'%d-%M');

update data_table
set dob_new= str_to_date(concat(temp_dob1,'-',substring_index(temp_dob2,'-',-2)),'%Y-%m-%d');

all is well nw...  :)
Hope it helps smone
[15 Dec 2007 15:24] Peter Laursen
Fomatting issue was in what I wrote!
The function name will have to be followed by "(" in most clients!
This is better:

SELECT 
  str_to_date(
    concat( 
      SUBSTRING('01-JAN-69',1,7),
      '19',
      SUBSTRING('01-JAN-69',8,2)
    )
   ,'%d-%M-%Y'
  );
[16 Dec 2007 4:16] MySQL Verification Team
Thank you for the bug report. !bug. The reason for already commented by Peter.