Skip to content

G-9040: Try using FX in string to date/time conversion format model to avoid fuzzy conversion.

Blocker

Reliability, Testability

Reason

The default string-to-date conversion rules allow fuzzy conversion when converting from strings to date or timestamp datatypes (using to_date, to_timestamp, to_timestamp_tz or cast to any of those datatypes). For example you can omit punctuation characters, use any non-alphanumeric character for punctuation, use month name instead of number, or various other rules.

In practice you almost always expect a truly fixed format and want the database to enforce the format model and raise an error if the data does not match the format model. This you can achieve by adding the format modifier FX (format exact).

The exception to this rule can be if you are converting textual input typed by a user, in which case the fuzzy conversion may be what you want.

Example (bad)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
create or replace package body employee_api is
   procedure set_dob(
      in_employee_id in employees.employee_id%type
     ,in_dob_str     in varchar2
   ) is
      co_employee_id constant employees.employee_id%type := in_employee_id;
      co_dob_str     constant type_up.date_string        := in_dob_str;
   begin
      update employees
         set date_of_birth = to_date(
                co_dob_str default null on conversion error
               ,'YYYY-MM-DD'
             )
       where employee_id = co_employee_id;
   end set_dob;
end employee_api;
/

Example (good)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
create or replace package body employee_api is
   procedure set_dob(
      in_employee_id in employees.employee_id%type
     ,in_dob_str     in varchar2
   ) is
      co_employee_id constant employees.employee_id%type := in_employee_id;
      co_dob_str     constant type_up.date_string        := in_dob_str;
   begin
      update employees
         set date_of_birth = to_date(
                co_dob_str default null on conversion error
               ,'FXYYYY-MM-DD'
             )
       where employee_id = co_employee_id;
   end set_dob;
end employee_api;
/