Results 1 to 8 of 8
  1. #1
    Join Date
    May 2011
    Posts
    24

    Unanswered: date validation in MySQL

    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?
    Last edited by reeson; 06-01-11 at 04:52.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2011
    Posts
    24
    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;

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    did you try my suggestion?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2011
    Posts
    24
    i'm sorry but i cant even do step 1 because i dont have a DATE column in the table.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2011
    Posts
    24
    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.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by reeson View Post
    thanks, i tried using str_to_date but seems like it doesn't really check for valid date.
    did you change the server mode to STRICT_ALL_TABLES ?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •