SYSCREATEDATE is saved as varchar2 in DB. Value is as follows:

SYSCREATEDATE
10/17/2006 7:44
11/29/2011 6:17
11/16/2011 11:46
2/27/2012 4:01
2/27/2012 4:01
2/27/2012 4:01

Now i want to convert the field to timestamp and pick dates less than a certain date say '06/01/2020'.

Tried this:

select * from legacy_case where  TO_TIMESTAMP(SYSCREATEDATE, 'MM/DD/YYYY HH:MI:SS') < '06/01/2020 00:00:00';

I am getting the below error:

ORA-01843: not a valid month
01843. 00000 -  "not a valid month"
*Cause:    
*Action:

Any input please?

CodePudding user response：

Several issues/comments:

1. never store dates in varchar2 fields. use a proper DATE or TIMESTAMP data type to avoid parsing errors to begin with. This is just asking for trouble. Better to enforce validation with the proper data types.

2. Is the date format in this field 12 hour AM/PM or 24 hours? I don't see any values > 11:xx so can't tell, but HH format is only for hours 1-12. If the timestamp string is 24 hour format, then you need to use HH24.

3. Never use string literals in a comparison, always convert using to_date or to_timestamp, as the literal may not match the current NLS_DATE_FORMAT default and may not be parsed properly. You can see the default format with the following query:

select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';

CodePudding user response：

Convert a sting literal to timestamp explicitly

select * 
from legacy_case 
where  TO_TIMESTAMP(SYSCREATEDATE, 'MM/DD/YYYY HH:MI:SS') < TO_TIMESTAMP('06/01/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS')