CODEWITHSUNDEEP
sqloracle-databasedate
Bhavik
Bhavik

Reputation: 346

SQL Date format not changing

I want to do a simple date format change.

My query looks like this:

select to_date('01-JAN-2018','dd-mm-yyyy') from dual.

This is part of a much bigger query.

This is giving error like below on my prod:

oracle.xdo.servlet.data.DataException: oracle.xdo.XDOException: java.sql.SQLDataException: ORA-01858: a non-numeric character was found where a numeric was expected

This exact same is working fine on my offline oracle 10g

Upvotes: 0

Views: 282

Answers (3)

DeadCat
DeadCat

Reputation: 192

try this : 

    select to_date('01-01-2018','dd-mm-yyyy') from dual

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

There's a mismatch with formatting mm and MON.

Use

select to_date('01-JAN-2018','dd-MON-yyyy') from dual

or

select to_date('01-01-2018','dd-mm-yyyy') from dual

In your oracle 10g db, nls_date_format must be dd-mm-yyyy ( or variants like dd.mm.yyyy or dd/mm/yyyy).

So, it doesn't care of dd-mon-yyyy(i.e. implicitly converts dd-MON-yyyy to dd-mm-yyyy)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Don't use to_date(). Instead use the date keyword:

select date '2018-01-01'

Alternatively, use the right format:

select to_date('01-JAN-2018', 'DD-MON-YYYY')
from dual

Upvotes: 3

Related Questions