oracle to_date returns some error in custom query

Question

Oracle DB

START_DATE          |END_DATE           
--------------------|-------------------
2016-02-01 00:00:00 |2016-02-29 23:55:00
2016-02-01 00:00:00 |2016-02-29 23:55:00
2016-02-01 00:00:00 |2016-02-29 23:55:00
2016-02-01 00:00:00 |2016-02-29 23:55:00
2016-02-01 00:00:00 |2016-02-29 23:55:00

Query

`select * from  VM_REPORT_TEMP_US where to_date(START_DATE,'YYYY-MM-DD HH24:MI:SS') >= '2016-02-01 00:00:00' AND to_date(END_DATE,'YYYY-MM-DD HH24:MI:SS') <= '2016-`02-24 24:59:00'`

Im trying to run this query but im getting some error. Can some one know where im going in this query ?

Im getting the below error

SQL Error [1861] [22008]: ORA-01861: literal does not match format string

java.sql.SQLDataException: ORA-01861: literal does not match format string

score 2 · Accepted Answer · 2016-06-24 12:22:49Z

2

Do NOT use to_date() with a DATE column. That first converts the date value into a varchar value just to convert that back to a date which it was to begin with.

to_date() expects a varchar value, so Oracle first converts the DATE value into a varchar value using the current NLS settings. Then it tries to convert that varchar back into a date, using the format mask you supplied which most probably doesn't match the default NLS format you have and therefor you get an error.

You should also use proper date values in the condition rather then strings that are (again) implicitly converted to a DATE based on the current NLS settings:

select * 
from VM_REPORT_TEMP_US 
where START_DATE >= timestamp '2016-02-01 00:00:00' 
  AND END_DATE <= timestamp '2016-02-24 23:59:00'

Note that the hour 24 is invalid in an ISO timestamp literal.

If you want to provide the date/timestamp value in a format other then the ISO format, you need to use to_date() for those:

select * 
from VM_REPORT_TEMP_US 
where START_DATE >= to_date('01.02.2016 00:00:00', 'dd.mm.yyyy hh24:mi:ss') 
  AND END_DATE <= to_date('24.02.2016 23:59:59', 'dd.mm.yyyy hh24:mi:ss')

edited Jun 24, 2016 at 12:22

answered Jun 24, 2016 at 12:18

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

Question User Over a year ago

it retruns some error like this

SQL Error [933] [42000]: ORA-00933: SQL command not properly ended    java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

Thorsten Kettner Over a year ago

Good explanation on what is happening in OP's query. I was just about to add that to my answer when you posted yours :-)

Thorsten Kettner Over a year ago

And I didn't spot the 24 hour either.

Thorsten Kettner · Accepted Answer · 2016-06-24 12:24:19Z

2

start_date and end_date are dates already, so you don't have to convert them to date. Convert the strings instead or even better use datetime literals.

select * 
from  VM_REPORT_TEMP_US 
where START_DATE >= timestamp '2016-02-01 00:00:00' 
and END_DATE <= timestamp '2016-02-24 00:59:00'

edited Jun 24, 2016 at 12:24

answered Jun 24, 2016 at 12:16

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Comments

Utsav · Accepted Answer · 2016-06-24 12:31:37Z

0

Bit different from both the above answers, but this is what I would use to avoid confusion between YYYY-MM-DD and YYYY-DD-MM.

select * from  VM_REPORT_TEMP_US 
where 
START_DATE >= to_date('2016-02-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
AND 
END_DATE   <= to_date('2016-02-24 00:59:00','YYYY-MM-DD HH24:MI:SS')

answered Jun 24, 2016 at 12:31

Utsav

8,1632 gold badges22 silver badges40 bronze badges

Collectives™ on Stack Overflow

oracle to_date returns some error in custom query

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related