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: | |
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
[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.