How to test if date format string is a valid date format string in Oracle

Question

I want users to be able to enter a date format string so they can specify how they want a date value to be displayed/entered.

How can I validate this date format string so that they can enter only a valid Oracle date format strings?

Presumably if they enter an invalid format, your code will throw an ORA-01821: date format not recognized error when you try to use it - can't you just pass this back to the calling app? — StevieG
– StevieG, Commented Nov 2, 2011 at 11:40
You also need to consider that there are date formats that are valid but practically useless e.g. 'DD', which would allow the user to enter '12' which would mean the 12th of the current month. I would therefore suggest that you present them with a list of permitted formats to choose from rather than letting them define their own. — Tony Andrews
– Tony Andrews, Commented Nov 2, 2011 at 11:49
Thanks, for your suggestions. A list of possible formats is probably the easiest. — Rene
– Rene, Commented Nov 2, 2011 at 13:37

Kevin Burton · Accepted Answer · 2011-11-02 19:26:16Z

7

you could create a function:

e.g:

FUNCTION is_valid_date_format ( 
    p_format IN VARCHAR2 ) 
RETURN BOOLEAN IS
    l_date VARCHAR2(100) := NULL;
BEGIN
    l_date := TO_char( sysdate, p_format );
    RETURN TRUE;
EXCEPTION
    WHEN OTHERS THEN
        RETURN FALSE;
END is_valid_date_format;

and use it like this

IF is_valid_date_format('dd/mm/yyyy') THEN

at the moment it will allow time formats too, however it would be simple to extend it to disallow a format that contains undesired formats e.g: hh hh24 mi ss

by adding: (you will probably want to uppercase your format string first)

IF INSTR(p_format,'HH')>0 OR INSTR(p_format,'HH24')>0 
OR INSTR(p_format,'MI')>0  OR INSTR(p_format,'SS')>0 THEN
    RETURN FALSE
END IF;

edited Nov 2, 2011 at 19:26

answered Nov 2, 2011 at 12:43

Kevin Burton

12k2 gold badges26 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rene Over a year ago

Thanks Kevin but this is not an answer to my question at all. I don't want to know if p_date_as_string matches p_format. I want to know if p_format is a valid format.

Collectives™ on Stack Overflow

How to test if date format string is a valid date format string in Oracle

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related