CODEWITHSUNDEEP
oracle-database
Santosh budhe
Santosh budhe

Reputation: 89

Confusion using to_date function to confirm date format

select  to_date('07/09/14','yyyy-mm-dd') from dual;

is returning 14-SEP-07

I was expecting it to thrown an exception as the date and the format requested are not the same. Secondly we have slashes in the input date and hypen in the format.

Can someone tell me how to confirm if the input value is of the provided format.

Upvotes: 1

Views: 414

Answers (3)

Will Marcouiller
Will Marcouiller

Reputation: 24142

TO_DATE() only takes a string and convert it to a date/time in respect to the given format your provided. If it can't find the exact format, it will do its best to determine what could it be. Given your 'yyyy-mm-dd' format, Oracle deduces that the 07 is the year, and the 14 is the day.

You have to write your own function to throw an expected exception or the like to handle the issue, if there is any based on your functional requirements.

So when you select the resulting date, the format that is displayed is actually based on your NLS_DATE_FORMAT system parameter, which gives your resulting date.

Upvotes: 0

Joshua Huber
Joshua Huber

Reputation: 3533

REGEXP_LIKE can somewhat do this. Note, this is basic, since it would accept values like 2014-0-32. However, those insane values would fail in whatever you do next in your code, such as to_date().

SELECT 'Yes, valid boss.' is_valid FROM DUAL
  WHERE regexp_like('07/09/14','^[0-9]{4}-[0-9]{1,2}-[0-9]{1,2}$');

no rows selected

.

SELECT 'Yes, valid boss.' is_valid FROM DUAL
  WHERE regexp_like('2014-07-09','^[0-9]{4}-[0-9]{1,2}-[0-9]{1,2}$');

IS_VALID
----------------
Yes, valid boss.

.

SELECT 'Yes, valid boss.' is_valid FROM DUAL
  WHERE regexp_like('2014-7-9','^[0-9]{4}-[0-9]{1,2}-[0-9]{1,2}$');

IS_VALID
----------------
Yes, valid boss.

EDIT: and if you're into PL/SQL, you can do a regex match and throw your own exception ...

DECLARE
  v_is_valid INTEGER;
BEGIN
  SELECT count(*) INTO v_is_valid FROM DUAL
    WHERE regexp_like('07/09/14','^[0-9]{4}-[0-9]{1,2}-[0-9]{1,2}$');
  IF v_is_valid = 0 THEN
    raise_application_error (-20400, 'Exception: date was given in the wrong format.');
  END IF;
END;
/

*
ORA-20400: Exception: date was given in the wrong format.

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231781

to_date is relatively liberal in attempting to convert the input string using the provided format mask. It generally doesn't concern itself with the specific separator character in the string or in the format mask-- your string could use dashes or slashes or, heck, asterixes if you wanted. Of course, that can mean that you get unexpected results. In this case, for example, the date that is created is in the year 7 (i.e. 2007 years ago). That is a valid date in Oracle but it is highly unlikely to be the date you expect unless you're storing data about ancient Rome.

What, exactly, does it mean to you to "confirm if the input value is of the provided format"? Depending on what you are looking for, you may want to use regular expressions.

Upvotes: 1

Related Questions