Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    41

    How to check for data format in pl/sql

    Hi All,

    I want to find out the date format.
    say i have some date parameter for a procedure,
    i want to ensure date value passed is of format 'YYYY-MM-DD'.
    if not in the above format i have to return message date of improper format from my procedure.

    can anyone help me how to achieve this.

    Thanks & Regards in Advance
    'A candle will loose nothing by lighting an another candle'

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Well, if the parameter passed is of type DATE (which seems appropriate), there is no format to check. Formats only apply to VARCHAR2 strings that are being converted to/from DATEs.

    If the parameter is actually VARCHAR2 for some reason, then the easiest way to check the format matches what you expect is to "suck it and see":
    Code:
    PROCEDURE myproc (p_datestr IN VARCHAR2)
    IS
      v_date DATE;
    BEGIN
      -- Check the date format
      BEGIN
        v_date := TO_DATE(p_datestr,'YYYY-MM-DD');
      EXCEPTION
        WHEN OTHERS THEN
          RAISE_APPLICATION_ERROR(-20001,'Date string not in correct format');
      END;
      ...
    END;

  3. #3
    Join Date
    Apr 2004
    Posts
    246
    You really should pass it as a date, and have your calling software convert it from char to date (if needed) because "2004-05-01" is valid as both may 1st and jan 5th.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  4. #4
    Join Date
    Feb 2004
    Posts
    41
    Thank you very much for the replies, but i think i am not clear with the question,
    now with this procedure i will explain my requirement.

    PROCEDURE myproc (p_datestr IN DATE)
    IS
    v_date DATE;
    BEGIN
    -- Check the date format
    BEGIN
    v_date := TO_DATE(p_datestr,'YYYY-MM-DD');
    EXCEPTION
    WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20001,'Date string not in correct format');
    END;
    ...
    END;

    now in the above procedure parameter p_datestr is of data type date,
    what i want to do is i want to confirm whether the p_datestr of of format
    'YYYY-MM-DD'.

    example: if parameter p-datestr is passed with value '27-10-2004',
    but i want to check whether the format of p_datestr is of
    format 'YYYY-MM-DD', since it not of the format i want to return
    some error message.

    what ever the solution given previously is to check whether the value of some date format.
    my doubt is how can iassure date is of a particular format.


    help will be highly appreciated.
    thanks
    'A candle will loose nothing by lighting an another candle'

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    But it is meaningless to "check the format" of a DATE parameter, because a DATE parameter is "unformatted". See what I mean:
    Code:
    SQL> create or replace procedure p (p_date in date) is
      2  begin
      3    dbms_output.put_line('Date is...      '||to_char(p_date,'DD-MON-YYYY'));
      4    dbms_output.put_line('Date is also... '||to_char(p_date,'YYYY-MM-DD'));
      5    dbms_output.put_line('As well as...   '||to_char(p_date,'YYYY-DD-MM'));
      6    dbms_output.put_line('Or perhaps...   '||to_char(p_date,'DD MONTH YYYY HH24:MI:SS'));
      7  end;
      8  /
    
    Procedure created.
    
    SQL> exec p(sysdate);
    Date is...      27-OCT-2004
    Date is also... 2004-10-27
    As well as...   2004-27-10
    Or perhaps...   27 OCTOBER   2004 13:07:08
    
    PL/SQL procedure successfully completed.
    By the time your procedure receives the DATE parameter, the calling program has already done any necessary conversion from formatted text to DATE, and the DATE is sure to be valid.

    Further examples of calls to that procedure. The first uses the "default" format mask, the second specifies a different one.
    Code:
    SQL> exec p('01-jan-1979')
    Date is...      01-JAN-1979
    Date is also... 1979-01-01
    As well as...   1979-01-01
    Or perhaps...   01 JANUARY   1979 00:00:00
    
    PL/SQL procedure successfully completed.
    
    SQL> exec p (to_date('1984-12-25','YYYY-MM-DD'))
    Date is...      25-DEC-1984
    Date is also... 1984-12-25
    As well as...   1984-25-12
    Or perhaps...   25 DECEMBER  1984 00:00:00
    
    PL/SQL procedure successfully completed.
    Last edited by andrewst; 10-27-04 at 09:14.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,434
    I have seen this misconception over and over during the years. A date field has no format, it is a 7 character binary value that stores a timestamp down to an accruracy of one second. Using to_char the date field can be displayed in a number of ways, but the underlaying date column or field never changes. Just like if you have a number field containing the value 123456.78

    You could display the field as
    123456.78
    123457 (rounded)
    $123,456.78 (currency)

    But the actual number never changes.
    Last edited by beilstwh; 10-27-04 at 10:31.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Oct 2004
    Posts
    1

    date format

    hello

    there is nothing like a date format.

    any date datatype is internally stored as a 7 byte data on Oracle
    database.

    the format only comes into picture when the parameter assigned to a date
    variable is of varchar2 type.
    other instances are where you need to print the date in a specific function
    in which you would first convert it into a varchar2 and then print it.

    if u are so keen on checking the format of the input parameter,pass it
    as a varchar2 rather than a date.

    regards
    d'evil

Posting Permissions

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