1
     begin
     if TO_CHAR('23-OCT-1994','DAY') = 'FRIDAY' THEN 
        DBMS_OUTPUT.PUT_LINE('FRIDAY');
     else
        DBMS_OUTPUT.PUT_LINE('Not Friday');
     end if;
     end;
     /

I am trying to see if 10/23/1994 is Friday. If it is Friday, it is going to return FRI, if not, NOT FRI

However I get
"PL/SQL: numeric or value error: character to number conversion error".

1

'23-OCT-1994' is a string, not a DATE, so TO_CHAR on a character is pointless.

First you have to convert your string into a DATE value. You should specify the date format plus the language which is used:

TO_DATE('23-OCT-1994', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American')

Then you can compare with your weekday. Note, 'DAY' returns the weekday but padded with spaces up to the longest name. Try '"'||TO_CHAR(DATE '2018-10-23', 'DAY')||'"' to see the effect. You should also specify the language.

You can use one of the following:

  • TRIM(TO_CHAR(..., 'DAY', 'NLS_DATE_LANGUAGE = American')) = 'FRIDAY'
  • TO_CHAR(..., 'fmDAY', 'NLS_DATE_LANGUAGE = American') = 'FRIDAY'
  • TO_CHAR(..., 'DY', 'NLS_DATE_LANGUAGE = American') = 'FRI'

for example

begin
   if TO_CHAR(TO_DATE('23-OCT-1994', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American'),'fmDAY', 'NLS_DATE_LANGUAGE = American') = 'FRIDAY' THEN 
      DBMS_OUTPUT.PUT_LINE('FRIDAY');
   else
      DBMS_OUTPUT.PUT_LINE('Not Friday');
   end if;
end;
|improve this answer|||||
0

Your problem is in using the TO_CHAR function on a text string which is interpreted as a string, rather than a date. This will throw the same error:

SELECT TO_CHAR('23-OCT-1994','DAY') FROM dual;

Instead, you'll need to use TO_DATE to convert the string to a date, then you would be able to use TO_CHAR on the output of that.

SELECT TO_CHAR(TO_DATE('23-OCT-1994', 'DD-MON-YYYY'),'DAY') FROM dual;

Rather than doing an IF statement with DBMS_OUTPUT.PUT_LINE(), you could do this with a simple SELECT statement using the CASE syntax:

SELECT CASE
          WHEN TO_CHAR(TO_DATE('23-OCT-1994', 'DD-MON-YYYY'),'DAY') = 'FRIDAY' 
               THEN 'Friday'
          ELSE 'Not Friday'
        END
FROM dual;
|improve this answer|||||
  • The language information is also missing. – Wernfried Domscheit Oct 29 '18 at 14:06
  • TO_CHAR(TO_DATE('23-OCT-1994', 'DD-MON-YYYY'),'DAY') gives "SUNDAY " - not "SUNDAY" – Wernfried Domscheit Oct 29 '18 at 15:49
0

TO_CHAR( DATE DATATYPE , 'DAY' ) ----> Gives DAY of DATE DATATYPE

The TO_CHAR function can extract DAY from DATE datatype. As you are passing STRING as the first parameter, it does not understand it.

Here, is the same using decode. You can very well do with the case or anonymous block.

select decode( to_char(to_date('23-OCT-1994', 'dd-MON-yyyy','NLS_DATE_LANGUAGE = American'), 'DAY')
                , 'FRIDAY'
                , 'Yes Friday'
                , 'No Friday')
          from dual;
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.