sql_dummy sql_dummy -3 years ago 102
SQL Question

Why sysdate-'dd-mm-yyy' is not valid in-spite of internal conversion being possible?

'dd-mm-yy'
being
NLS_DATE_FORMAT
it is implicitly converted to
Date
data type during comparison, insertion but why is not converted during a arithmetic operation.

sysdate>'01-01-17' //is valid
sysdate-'01-01-17' //is in valid


First I assumed the operators(+,-,..) are only for numeric data type. Later I got to know these operators are used even in Date Arithmetic and even operands with
Date
data type are also valid.

"During arithmetic operations on and comparisons between character and noncharacter datatypes, Oracle converts from any character datatype to a numeric, date, or rowid, as appropriate" -
doc

Using
to_date
solves the issue. I am looking for the reason why it is not implicitly converted.

Answer Source

Forget implicit conversion. Just express your dates using explicit date literals:

sysdate > date '2017-01-01'
sysdate - date '2017-01-01'

The code is clearer and less ambiguous as well.

As to why Oracle doesn't do implicit conversion in the second case. Oracle doesn't know what type to expect. The second operand could be either a date or a number, so it doesn't know how to convert the string. In the first case, the comparison should be to a date.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download