CODEWITHSUNDEEP
oracle-databaseplsql
Duleep
Duleep

Reputation: 587

How to check given string date is my dd-mm-yyyy format in PL/SQL

My date format is dd-mm-yyyy. there is a string date I want to check that string date is 'dd-mm-yyyy' if not( like dd/mm/yyyy, 10-OCT-2013) that function will return 'NO'

function checkMydateFormat(checkDate IN VARCHAR)VARCHAR{

IF(check date is 'dd-mm-yyyy')THEN
  RETURN 'YES';
ELSE
  RETURN 'NO';
END IF;

}

How can I write a PL/SQL function to check string date is correct format?

Upvotes: 0

Views: 22114

Answers (4)

Ramya Bharathi G
Ramya Bharathi G

Reputation: 1

create or replace function checkMydateFormat(checkDate IN VARCHAR2)
RETURN VARCHAR2
IS
  v_date    DATE;
BEGIN 
    v_date  := to_date(checkDate,'dd-mm-yyyy');
    RETURN 'YES';
  EXCEPTION
  WHEN OTHERS THEN 
    RETURN 'NO'; 
END checkMydateFormat;

Upvotes: 0

schurik
schurik

Reputation: 7928

you can try to convert the string to the date using the desired format, and catch Exceptions. Somthing like the following:

create function checkMydateFormat(checkDate IN VARCHAR2)
RETURN VARCHAR2
IS
  v_result VARCHAR2(3);
  v_date    DATE;
BEGIN 
  BEGIN 
    v_date  := to_date(checkDate,'fxdd-mm-yyyy');
    v_result := 'YES';
  EXCEPTION
  WHEN OTHERS THEN v_result := 'NO'; 
  END;
  RETURN v_result;
END;
/

Upvotes: 1

ajmalmhd04
ajmalmhd04

Reputation: 2602

CREATE OR REPLACE
FUNCTION checkMydateFormat(
          checkDate IN VARCHAR2)
     RETURN VARCHAR2
IS
     v_result VARCHAR2(3);
     v_date   DATE;
BEGIN
     v_date   := to_date(checkDate,'fxdd-mm-yyyy');
     V_RESULT := 'YES';
     RETURN V_RESULT;
EXCEPTION WHEN OTHERS THEN
     V_RESULT := 'NO';
     RETURN v_result;
END;

Upvotes: 0

i100
i100

Reputation: 4666

Try something like this

var par varchar2(20);
exec :par := '17-10-2013';
DECLARE
FUNCTION checkMydateFormat(checkDate IN VARCHAR2)
    RETURN VARCHAR2 IS RESULT VARCHAR2(10);
  BEGIN
    SELECT CASE
             WHEN regexp_like('7-10-2013', '\d{2}\-\d{2}\-\d{4}\', 'i') THEN
              'yes'
             ELSE
              'no'
           END into RESULT
      FROM dual;
    RETURN(RESULT);
  END checkMydateFormat;


BEGIN
  dbms_output.enable(NULL);
  dbms_output.put_line(checkMydateFormat(:par));

END;

Upvotes: 2

Related Questions