CODEWITHSUNDEEP
sqloracle-databaseconditional-statementsto-dateto-char
OWA
OWA

Reputation: 45

To_char and To_date

I'm struggling with one report contain a varchar2 column as a date in MON-YY format

what I wanna do is convert the value of that column into date so I can do some process on it such as filtering with > or < or even between and set two dates

My column name: BAL.PERIOD_NAME below is the condition cussing error ORA-01843: not a valid month

AND TO_DATE(BAL.PERIOD_NAME,'MON-YY')  > TO_DATE('FEB-18','MON-YY')

thanks in advance

Upvotes: 0

Views: 688

Answers (3)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

Storing date values as strings like 'FEB-18' is really a design flaw. You should consider a migration to DATE (or TIMESTAMP) data type.

  • Did you never heard about the Year-2000-Problem? Well, I assume when everybody in IT world was talking about the Y2K problem many present software developers were still kids.
  • The string can be malformed in any way.
  • Even if the string has correct format you may face problem with language settings.

Anyway, perhaps it is only a language issue. Try this one

WHERE TO_DATE(BAL.PERIOD_NAME,'MON-RR', 'NLS_DATE_LANGUAGE = American') > DATE '2018-02-01'

In case you are running Oracle 12.2 or later you can use function VALIDATE_CONVERSION:

WHERE VALIDATE_CONVERSION(BAL.PERIOD_NAME, 'MON-RR', 'NLS_DATE_LANGUAGE = American') = 1

If you don't have Oracle 12.2 yet then write your own function, for example like this:

CREATE OR REPLACE FUNCTION CONVERT_TO_DATE(str in VARCAHR2) RETURN DATE IS
BEGIN
   RETURN TO_DATE(str, 'MON-RR', 'NLS_DATE_LANGUAGE = American');
EXCEPTION
   WHEN OTHERS THEN
      RETURN NULL;  
END CONVERT_TO_DATE;

Upvotes: 1

EdHayes3
EdHayes3

Reputation: 1954

Make sure you are using the correct format mask for the date you are converting.

Works:

select  TO_DATE('feb-18','MON-YY') from dual

Throws "not a valid month" error:

select  TO_DATE('02-18','MON-YY') from dual

Upvotes: 4

sticky bit
sticky bit

Reputation: 37472

You can use a recursive CTE to check you table for invalif month names.

WITH cte(month)
AS
(
SELECT 1 month
       FROM dual
UNION ALL
SELECT month + 1 month
       FROM cte
       WHERE month + 1 <= 12
)
SELECT *
       FROM bal
       WHERE substr(period_name, 1, 3) NOT IN (SELECT to_char(to_date(month, 'MM'), 'MON')
                                                      FROM cte);

Upvotes: 1

Related Questions