CODEWITHSUNDEEP
sqloracle-database
junsid
junsid

Reputation: 355

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 dd/mm/yyyy

Upvotes: 0

Views: 191

Answers (2)

user330315
user330315

Reputation:

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

Upvotes: 3

Thomas
Thomas

Reputation: 366

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

Upvotes: 0

Related Questions