java dateformat with oracle format gives ORA-01722: invalid number-CodePudding

The date field in oracle DB is stored in '10-FEB-99' format. My calander function in UI accepts date in this format '02/30/2023'. When i search date in UI, i have to query the DB with date field from UI to what exists in DB.

select * from case where TO_CHAR(p.OCCASION_FROM_DATE,'dd-MON-yyyy') = TO_CHAR('02/10/1999','MM/dd/yyyy');

When I execute this query I get below exception

**ORA-01722: invalid number 00000 - "invalid number" Cause: The specified number was invalid. Action: Specify a valid number.

Tried this

select * from case where TO_CHAR(p.OCCASION_FROM_DATE,'dd-MON-yyyy') = TO_CHAR('02/10/1999','MM/dd/yyyy');

CodePudding user response：

The date field in oracle DB is stored in '10-FEB-99' format.

Is it? Perhaps. It depends on column datatype. What is it? If it is VARCHAR2, then yes - it might be stored that way, and that's wrong. You should always store dates into DATE datatype columns.

Let's presume it is a varchar2 column. In that case, you should match two values: first convert a string (02/10/1999) to a valid date value (using to_date with appropriate format model), and then back to string - again with format model that matches string stored into the column:

select * from case p 
where p.OCCASION_FROM_DATE = 
      to_char(to_date('02/10/1999','MM/dd/yyyy'), 'dd-mon-rr', 'nls_date_language = english);

On the other hand, if values stored in table are in date datatype column (yes, that should be the case!), note that dates aren't stored in human-readable format. That's a 7-byte value representing century/year/month/day/hour/minute/second. The fact that you see it as 10-feb-99 is because your current NLS settings say so.

Query then gets somewhat simpler because you just have to convert string 02/10/1999 to date:

select * from case p
where p.occasion_from_date = to_date('02/10/1999', 'mm/dd/yyyy')