sql - Oracle compare two different dates

Question

The following query cant execute from java . Here i use oracle xe server

datetrx <= to_date('2014-07-16 00:00:00.0','yyyy/mm/dd hh24:mi:ss.f')

datetrx is in the date format of dd/mm/yyyy

Answer 1

Your input string in the to_date() function does not match your pattern. The value contains - as the delimiter, however in the pattern you use /:

If you align your input format and the pattern, this should work:

datetrx <= to_date('2014-07-16 00:00:00','yyyy-mm-dd hh24:mi:ss')

I personally prefer ANSI timestamp literals over the to_date() function because they are portable and it's less typing:

datetrx <= timestamp '2014-07-16 00:00:00'

Note the format the string supplied here is always the ISO format.

---

A side note:
Any "format" you see when looking at the values in the column daterx is applied by the SQL client you use to display that data (SQL*Plus, SQL Developer, ...).

The value itself is stored without a format on the server. Formatting of a DATE value is always done by the SQL client (or your application):