Wrap Up Your Date – The Daily WTF

Today, we look at a simple bit of bad code. The badness is not that they’re using Oracle, though that’s always bad. But it’s how they’re writing this PL/SQL stored function:

  FUNCTION CONVERT_STRING_TO_DATE 
    (p_date_string IN Varchar2,
       p_date_format IN Varchar2 DEFAULT c_date_format)
    Return Date

   AS

   BEGIN

    If p_date_string Is Null Then
        Return Null;
      Else
        Return To_Date(p_date_string, p_date_format);
      End If;

   END;  

This code is a wrapper around the to_date function. The to_date function takes a string and a format and returns that format as a date.

This wrapper adds two things, and the first is a null check. If the input string is null, just return null. Except that’s exactly how to_date behaves anyway.

The second is that it sets the default format to c_date_format. This, actually, isn’t a terrible thing. If you check the docs on the function, you’ll see that if you don’t supply a format, it defaults to whatever is set in your internationalization settings, and Oracle recommends that you don’t rely on that.

On the flip side, this code is used as part of queries, not on processing input, which means that they’re storing dates as strings, and relying on the application layer to send them properly formatted strings. So while their to_date wrapper isn’t a terrible thing, storing dates as strings definitely is a terrible thing.