Oracle Compare Two Different Dates

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

Solution 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):

Solution 2:

please specify your ask, its hard to figure out what do you want... first of all this isnot a verified date format

just type it and try it out with DUAL:

SELECT  to_date('2014-07-16 00:00:00.0', 'YYYY/MM/DD HH24:MI:SS.F') 
FROM dual;

delete the '.F' part from it