Been googling around but cant seem to find a solution for this problem.
I'm writing a function to convert from code to date. The code will consist 6 character in the form of 'YYMMDD'.
I would like to put an exception that all invalid date like e.g: 2011-02-29 (the code would be 110229) to be converted to '00-00-00'.
Here is the code so far
create function CodeToDate (code char(6))
declare dt date;
declare datestring char(10);
if length(code) != 6 then set dt = '00-00-00';
elseif code not REGEXP '^-?[0-9]+$' then set dt = '00-00-00';
set datestring = concat(substr(code,1,2),'-',substr(code,3,2),'-',substr(code,5,2));
set dt = ifnull(date_format(datestring,'%y-%m-%d'),'00-00-00');
But when i do :
I'll get ERROR 1292 (22007): Truncated incorrect datetime value: '11-02-29'
I believe date_format() will raise an error if datestring is not a valid date. I was hoping it will return a null instead, hence why I use the ifnull() there.
Think you misunderstood me, I'm not trying to save the converted date into a table.
Let say I have a table 't1' with only one column, 'code'. I want to display (in mysql console) the date equivalent to that code. If the code produce invalid date, say '110229' will produce '11-02-29', it will display '00-00-00'.
The query will be like:
select CodeToDate(code) from t1;