is date() function for Oracle
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: is date() function for Oracle

  1. #1
    Join Date
    Jul 2000
    Posts
    41

    Question

    Hi,

    I am trying to write the Oracle equivalent function for isdate() of SQLServer. I have a field in Oracle as varchar2 that contains formated string dates as 'DD-MM-YYYY'. I need to chek to see if the dates are valid before pushing it into Oracle date field.



    create or replace function check_dateformat(p_date_input varchar2)
    return boolean is lv_date_return DATE ;
    begin
    lv_date_return := to_date(p_date_input, 'DD-MM-YYYY');
    if
    substr(p_date_input,1,2) not between '01' and '12'
    or
    substr(p_date_input,4,2) not between '01' and '31'
    or
    substr(p_date_input,7,4) not between '1900' and '2050'
    or
    substr(p_date_input,1,2) = '02' and substr(p_date_input,4,2) = '29'
    and round(substr(p_date_input,7,4)/4)*4 <> (p_date_input,7,4)
    --length(substr(p_date_input, instr(p_date_input, '-',1,2)+1))<>4
    Then
    return false;
    else
    return true;
    end if;
    exception
    When Others then
    return false;
    end check_dateformat;

    Can anyone help? Thanks,
    CD

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Isn't this one more simple:
    Code:
    create or replace function check_dateformat(p_date_input varchar2) 
    return boolean is
      lv_date_return DATE ;
      return_value BOOLEAN := TRUE;
    BEGIN
      BEGIN
        lv_date_return := TO_DATE (p_date_input, 'DD-MM-YYYY'); 
        IF lv_date_return NOT BETWEEN TO_DATE ('01-01-1900', 'DD-MM-YYYY') AND TO_DATE ('01-01-1900', 'DD-MM-YYYY') THEN
          return_value := FALSE;
        END IF; 
      EXCEPTION
        WHEN OTHERS THEN return_value := FALSE;
      END;
    RETURN return_value;
    END;
    Why even bother with checking months and day of months, with leap years and so on - it is all implicitly checked in a TO_DATE function.

    I only left the year range checking if you realy need to check this, otherwise you should simply throw that part out.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Aug 2000
    Posts
    194
    Ques:

    Does Oracle allows you to return BOOLEAN?

    thanks,

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Actually, that has a 2-part answer

    Within PL/SQL : Yes
    To the outside world: No

    So if you plan on calling this function from somewhere other than PL/SQL, then maybe use 1/0 or Y/N instead.

    - Chris

Posting Permissions

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



Click Here to Expand Forum to Full Width