1

I work in T-SQL but have been given some Oracle PL-SQL for review on a Project.

Within the code there are Multiple WHERE clauses that do comparison of a Field of DataType = DATE against Strings which hold a "date".

ex: 

       WHERE to_date(mytable.mydatefield) > '23-OCT-2015'

OR

       WHERE mytable.mydatefield > '23-OCT-2015'

Q1: Since "mydatefield" is already defined as a DATA type, isn't doing a "to_date" unnecessary?

Q2: Will Oracle do an implicit conversion on the '23-OCT-2015' and convert it to a date for comparison? I seem to remember encountering this before and comparing DATES to STRINGS caused issues? Am I incorrect about that? If not can someone give me an example that I can use as evidence that it would not work?

JTSOne
  • 169
  • 5
  • 19
  • 1
    `'23-OCT-2015'` is not a **DATE**, it is a **STRING**. Use **ANSI Date literal** `WHERE mytable.mydatefield > DATE '2015-10-23'` If you wantot use **TO_DATE**, use it on the R.H.S. `WHERE mytable.mydatefield > to_date('23-OCT-2015', 'DD-MON-YYYY)` – Lalit Kumar B Jan 11 '16 at 15:39
  • You do not need to convert the date field to date, that is true, though there is no harm in that. You are also right about the implicit conversion of the text to date. And I can tell you that implicit dates conversions could be hell and I believe that it is better to always avoid them. Actually, I think it would be more appropriate to use to_date on the text date with specified format string (`to_date('23-OCT-2015', DD-MON-YYYY')`). Implicit conversion will work in some/most cases - depending on the session's NLS settings. – stee1rat Jan 11 '16 at 15:41
  • @stee1rat there is some potential for harm in converting the date to a string and then back to a date. The default value of the `NLS_DATE_FORMAT` setting is `'DD-MON-RR'` a two digit year, so when the date is converted to a string the century is likely to be lost and replaced with either 1900 or 2000 when it's converted back to a date depending on the value of the decade. – Sentinel Jan 11 '16 at 16:09
  • @Sentinel Yes, I am sorry, you are right, they even mention it in the documentation: `Do not use the TO_DATE function with a DATE value for the char argument. The first two digits of the returned DATE value can differ from the original char, depending on fmt or the default date format.` http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions203.htm#SQLRF06132 . So there is harm even there :) – stee1rat Jan 11 '16 at 16:17
  • I think it has to be mentioned at least once here: 'DATA' is not a type. (Maybe it is a type and it was meant to be 'DATE' type.) – Makan Jan 18 '22 at 09:54

2 Answers2

4

A1: In general yes, but take the way Oracle handles implicit type conversions into account. The To_Date function around the mydatefield column expects a string input, so Oracle implicitly converts mydatefield to a string with a format matching the NLS_DATE_FORMAT session setting (which defaults to DD-MON-RR). Once converted to a string the To_Date function then converts it back to a date again using the current NLS_DATE_FORMAT setting. The newly reconstituted date is then compared to the string '23-OCT-2015', but since dates and strings aren't directly comparable the string value gets implicitly converted to a date using the current NLS_DATE_FORMAT setting. Depending on the value of the NLS_DATE_FORMAT setting, the first implicit conversion is likely to lose information specifically any time portion AND the original century, since the default NLS_DATE_FORMAT uses only a two digit year RR and no time component.

A2: Possibly, but it's best not to rely on it.

Both relations are poor programming for a couple of reasons. First they both are affected by implicit type conversions from dates to strings (or vice versa). Second they are both attempting to compare dates with strings in a non canonical form. As such 10-DEC-15 is less than 23-OCT-2015 because 1 is less than 2. Also note the difference in the number of digits representing the year since the default NLS_DATE_FORMAT uses a two digit year.

The correct method would be to compare the date column (possibly truncated) to a date string explicitly converted to a date

WHERE mytable.mydatefield > TO_DATE('23-OCT-2015', 'DD-MON-YYYY')

OR with truncation:

WHERE trunc(mytable.mydatefield) > TO_DATE('23-OCT-2015', 'DD-MON-YYYY')

which removes the time component of the date field.

Sentinel
  • 6,379
  • 1
  • 18
  • 23
  • +1 Good answer. However, as '23-OCT-2015' is a literal, we should replace it with `date '2015-10-23'`, so as to have a date literal. If we still wanted to use the string and convert it with TO_DATE for some reason, we should specify the NLS_DATE_LANGUAGE in the TO_DATE function, so as not to be dependent on session settings (especially with a month name OCT which would result in an exception in many languages): `TO_DATE('23-OCT-2015', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN')`. – Thorsten Kettner Jan 11 '16 at 16:16
  • @Sentinel, are you sure about the implicit string to date conversions? On [this page](https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements002.htm) they say that `The following rules govern the direction in which Oracle Database makes implicit datatype conversions: When comparing a character value with a DATE value, Oracle converts the character data to DATE.` . There's even example of that behavior. – stee1rat Jan 11 '16 at 16:27
  • @stee1rat it looks like you're right about the implicit conversion of strings to dates. However, it's still better practice not to rely on implicit conversions. – Sentinel Jan 11 '16 at 17:01
  • @Sentinel can't argue with that! – stee1rat Jan 11 '16 at 17:03
0

Q1: According to Oracle, the first parameter of to_date() is a char value. Using it like to_date(date_value) you will force an implicit cast of date_value to char and then wrapping it again in a date value.

Q2: The server will do an implicit conversion from the string '23-OCT-2015' to a date value but based on database parameters which can differ from various servers(DEV vs PROD for example) so you should not rely on them. An example of correct usage would be WHERE mytable.mydatefield > to_date('23-OCT-2015','dd-MON-yyyy')

You should always use to_date/to_char to make sure you are using the correct format. Please see this answer for a more detailed explanation: Comparing Dates in Oracle SQL

Community
  • 1
  • 1
Emil Moise
  • 373
  • 1
  • 8
  • Dear Sentinel! The database WILL implicitly convert a string to date. As stee1rat already mentioned, [Oracle docs](https://docs.oracle.com/database/121/SQLRF/sql_elements002.htm#g195937) clearly states that `When comparing a character value with a DATE value, Oracle converts the character data to DATE`. After reviewing, I would appreciate you edit your comment and withdraw your -1! – Emil Moise Jan 12 '16 at 07:35