Date Format validation on Standard Free Text Fields

By | November 3, 2014

I had a requirement to validate date entered in a free text field (standard field on a standard form). I have solved it by writing a function to validate the date format entered and called the function from forms personalization.

Here is a function to validate whether date entered is in valid format (‘MM/DD/YYYY’) or not.

What does this function do?

This function assumes that input is in format (‘MM/DD/YYYY’) and negates with sysdate which should either return a positive or a negative number. It goes into exception “ORA-01843: not a valid month” when input is not in expected format.

Function Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
CREATE OR REPLACE FUNCTION date_format_val_f(
    p_result_date VARCHAR2)
  RETURN VARCHAR2
  --
  -- +=========================================================================
  -- | Name       : DATE_FORMAT_VAL_F
  -- | Description: Function to validate date entered in Result field of Sample
  -- |              results
  -- | Author     : Shailender Thallam
  -- +=========================================================================
  --
IS
  l_date_diff VARCHAR2(20);
  --
BEGIN
  --
  --Date Validation
  --
    BEGIN
      SELECT trunc(sysdate - to_date(p_result_date,'MM/DD/YYYY'),0)
      INTO l_date_diff
      FROM dual;
    EXCEPTION
    WHEN OTHERS THEN
      --
      l_date_diff := 'FAIL';
      --
    END;
    --  
    RETURN l_date_diff;
  --
END date_format_val_f;

Testing:

1.Date_Format_Validation

2.Date_Format_Validation

WOW! Did you like this post? We'll send more interesting posts like Date Format validation on Standard Free Text Fields to you!
Enter your Email Address: