MySQL Bugs: #33259: str_to_date conversion of date format 'dd-mmm-yy' to 'yyyy-mm-dd'

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

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'

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.

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)

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

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

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'
  );

Thank you for the bug report. !bug. The reason for already commented by Peter.