This discussion is archived
9 Replies Latest reply: Aug 14, 2009 5:16 AM by sql_coder RSS

validate a string for a Date format

sql_coder Newbie
Currently Being Moderated
Hello,

I got a string in a PL/SQL Routine and need to know if it is a proper date format or not. Is there any function or regular expression for it, so I can check the string before I Insert it into a column with a date type ?

Ilja
  • 1. Re: validate a string for a Date format
    Hoek Guru
    Currently Being Moderated
    Hi,

    See Re: regarding checking date formats for Frank Kulash's function.
  • 2. Re: validate a string for a Date format
    Karthick_Arp Guru
    Currently Being Moderated
    What is the expected format? If you know that then you can define a variable of DATE datatype and use TO_DATE to validate it.
    dt_variable := TO_DATE(<INPUT_DATE>,<DATE_FORMAT>)
    If the date input is not in proper format the it will give error. You can catch it in an exception block and handle it.
  • 3. Re: validate a string for a Date format
    Centinul Guru
    Currently Being Moderated
    There are multiple ways you could accomplish that.

    1. You could use a TO_DATE() function and capture the error as shown below:
    SQL > DECLARE
      2          bad_month_format        EXCEPTION;
      3          PRAGMA EXCEPTION_INIT(bad_month_format,-01843);
      4          sample_date             DATE;
      5  BEGIN
      6          sample_date := TO_DATE('99/01/2009','MM/DD/YYYY');
      7  EXCEPTION
      8          WHEN bad_month_format THEN
      9                  DBMS_OUTPUT.PUT_LINE('Caught bad date format!');
     10  END;
     11  /
    Caught bad date format!
    
    PL/SQL procedure successfully completed.
    However the problem with this is that you must now every combination of how a date format can be incorrect (bad month, bad year, bad day of month, etc) so it may not be effective.

    2. Let the error propagate back up to the client upon insert and handle it there.
  • 4. Re: validate a string for a Date format
    babaravi Journeyer
    Currently Being Moderated
    hi,

    Against which date format you need to check?

    Edited by: Baba Ravi on Aug 14, 2009 4:22 AM
  • 5. Re: validate a string for a Date format
    Frank Kulash Guru
    Currently Being Moderated
    Ikrischer wrote:
    Hello,

    I got a string in a PL/SQL Routine and need to know if it is a proper date format or not. Is there any function or regular expression for it, so I can check the string before I Insert it into a column with a date type ?

    Ilja
    Hi, Ilja,

    Yes: you can call TO_DATE.

    For example, if you want to do the INSERT whether or not string_var contains a valid date:
    BEGIN
        ...
        BEGIN
            date_var := TO_DATE (string_var, 'DD-Mon-YYYY HH24:MI:SS;);   -- or whatever format
        EXCEPTION
            WHEN OTHERS THEN
                dbms_output.put_line ('Can''t insert bad date: ' || string_var);
        END;
    
        INSERT INTO my_table (..., dt_col)
                    VALUES (..., date_var);
        ...
    END;
    This is one of the few times I would use "EXCEPTION WHEN OTHERS THEN", because there are a lot of different errors could be raised by TO_DATE. Make sure the only statement in that BEGIN block is the assignment with TO_DATE.

    Edited by: Frank Kulash on Aug 14, 2009 7:23 AM
  • 6. Re: validate a string for a Date format
    Sven W. Guru
    Currently Being Moderated
    Which of the following strings would you consider proper and which not?

    01-01-01
    01-JAN-01
    01-01-0001
    0001-01-01
    01.01.01
    01-01-01 01:01:01
    1st JAN 0001
    31-01-01
    01-31-01
    01-01-31
    31-02-01
  • 7. Re: validate a string for a Date format
    sql_coder Newbie
    Currently Being Moderated
    thanks all for the feedback so far, maybe I should give a bit more information about my situation. I have more then 1000 rows in a table, one column is a possible date, but the data type is VARCHAR2 for a good reason. what I want to make is one! sql statement, that returns me every row where the column does not has a correct date. the format could be different, for example its 01.01.2009 or 01.01.09, what it makes a bit more complicate. I hoped there is a string function already like IS_DATE so I could use it in my qeury. but it looks like I have to write my own function and return 0 for a correct date and 0 if not.

    Ilja
  • 8. Re: validate a string for a Date format
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Ilja,
    Ikrischer wrote:
    ... I hoped there is a string function already like IS_DATE so I could use it in my qeury. but it looks like I have to write my own function and return 0 for a correct date and 0 if not.
    No, there's nothing I know about that includes more than one format.
    You can write something that loops through any number of formats, in order, and returns one value as soon as it finds a format that works with the given string, or returns something else if all the formats fail. You woud have to list all the possible formats you want it to test.

    What are you going to do with '01/02/2009'? It matches 'dd/mm/yyyy' and 'mm/dd/yyyy' formats equally well. Is '01/02/2009' January 2 or February 1?
  • 9. Re: validate a string for a Date format
    sql_coder Newbie
    Currently Being Moderated
    yes, its a problem when you dont have a format, so I have to be a bit more restrict with the import and have a fixed format DD.MM.YYY, then work with a created function.

    thanks all
    Ilja

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points