CODEWITHSUNDEEP
oracle-database
capser
capser

Reputation: 2635

Date mapping or translation in ORACLE

I have to use a schema on the table FOOTRADE. When I look for a specific tradeid, it works fine, in this case it returns 1/7/2020.

select FOODATE from CASPER.FOOTRADE where tradeid = '0000000001';
FOODATE
========
1/7/2020

However when I want to look for all the rows that contains foodate 1/7/2020 - I get an error ORA-01843: not a valid month

select * from CASPER.FOOTRADE  where TRADEDATE = '1/7/2020';

I suspect that the column is configured to somedate format and there has to be some mapping translation that needs to be done

Upvotes: 0

Views: 49

Answers (1)

OldProgrammer
OldProgrammer

Reputation: 12169

Assuming TRADEDATE is actually an oracle DATE type, then you should not use direct literals to compare. Instead do:

where TRADEDATE = to_date('01/07/2020','MM/DD/YYYY');

The default date format mask is defined in NLS_DATE_FORMAT. So if that format does not match with your input you get an error. It is best to never assume what the default format may be on a system and do a to_date() call on the string.

Note that you can override the default and change the NLS_DATE_FORMAT for a session via:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD'  .. etc whatever mask you need.

Upvotes: 2

Related Questions