• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 466
  • Last Modified:

Need to know if string value has date format

Hello,
I'm trying to know if a string value has date format. I use a function Is_Date to get that result, but no success.

select is_date('01/02/90xxx','DD/MM/YYYY HH24MISS') from dual;

Get error: ORA-01830: date format picture ends before converting entire input string


FUNCTION is_date (p_inDate DATE, p_format VARCHAR2)
      RETURN NUMBER
   AS
      v_dummy   DATE;
   BEGIN
      BEGIN

      SELECT TO_DATE (p_inDate, p_format) INTO v_dummy FROM DUAL;

      RETURN 1;
      EXCEPTION
         WHEN VALUE_ERROR
         THEN
            v_dummy := NULL;
      RETURN 0;
      END;

      RETURN v_dummy;
   END is_date;
0
miyahira
Asked:
miyahira
  • 2
2 Solutions
 
slightwv (䄆 Netminder) Commented:
If the input parameter is a DATE, it must be valid.

Check out:
create or replace FUNCTION is_date (p_inDate varchar2, p_format VARCHAR2)
      RETURN NUMBER
   AS
      v_dummy   DATE;
   BEGIN
      BEGIN

      v_dummy := TO_DATE (p_inDate, p_format);

      RETURN 1;
      EXCEPTION
      when others then
            RETURN 0;
      END;
END is_date;
/

show errors



select is_date('01/02/90xxx','DD/MM/YYYY HH24MISS') from dual;
0
 
sdstuberCommented:
try something like this  ( I fixed some syntax errors too)


CREATE OR REPLACE FUNCTION is_date(p_indate VARCHAR2, p_format VARCHAR2)
    RETURN NUMBER
AS
    v_dummy   DATE;
BEGIN
    v_dummy  := TO_DATE(p_indate, p_format);

    RETURN 1;
EXCEPTION
    WHEN OTHERS
    THEN
        RETURN 0;
END is_date;
0
 
sdstuberCommented:
oops, too slow

same idea, but I removed the extra begin/end block which wasn't needed
0
 
miyahiraAuthor Commented:
Thanks to both of you. I was blind, didn't see that I declared parameter p_indate as DATE and should be VARCHAR2
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now