date validation in MySQL

reeson · #1 (**permalink**) 06-01-11, 03:47

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

Code:

delimiter //
create function CodeToDate (code char(6))
returns date
begin
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';
else
	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');
end if;
return dt;
end //
delimiter ;

But when i do :

Code:

select CodeToDate('110229');

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.

Any workaround for this?

r937 · #2 (**permalink**) 06-01-11, 06:11

step1: declare the date column as NOT NULL DEFAULT '0000-00-00'

step2: make sure that the STRICT_ALL_TABLES server mode is on

step3: use STR_TO_DATE to convert your strings to dates

that's all there is to it!!

reeson · #3 (**permalink**) 06-01-11, 06:31

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;

r937 · #4 (**permalink**) 06-01-11, 06:35

did you try my suggestion?

reeson · #5 (**permalink**) 06-01-11, 06:40

i'm sorry but i cant even do step 1 because i dont have a DATE column in the table.

r937 · #6 (**permalink**) 06-01-11, 07:19

no, i meant using the STR_TO_DATE function

check it out, the manual has a good description of how it works on invalid dates

reeson · #7 (**permalink**) 06-01-11, 22:17

thanks, i tried using str_to_date but seems like it doesn't really check for valid date.

for example:
11-02-31 -> 2011-02-31
05-06-31 -> 2005-06-31

only really ridiculous date like 17-56-44 will return null.

i'm using version 5.0.89 by the way, if it matters.