CODEWITHSUNDEEP
oracle-database
newbee
newbee

Reputation: 25

ORA-01846: not a valid day of the week

    SQL> create or replace procedure test ( newcol in varchar2,fld_timestamp out timestamp ) as 
      2  begin
      3  fld_timestamp := to_timestamp_tz('newcol','DAY Mon DD hh24:mi:ss TZD YYYY');
      4  end;
      5  /

    Procedure created.

    SQL> declare
     2  fld_timestamp timestamp;
     3  begin
     4  test('WEDNESDAY Jan 13 16:29:00 EST 2016',fld_timestamp);
     5  end;
     6  /
    declare
   *
   ERROR at line 1:
   ORA-01846: not a valid day of the week
   ORA-06512: at "SANPALLE.TEST", line 3
   ORA-06512: at line 4

I have created a procedure named test to convert the char data into timestamp . The input i am giving to procedure is in correct format but i still getting "not valid day of the week" , can someone please tell me where i am doing wrong ?

Upvotes: 1

Views: 1753

Answers (1)

Sameer Mirji
Sameer Mirji

Reputation: 2245

Remove the ' quotes on 'newcol'. Also use TZR instead of TZD in the format.

It should be like this:

3  fld_timestamp := to_timestamp_tz(newcol,'DAY Mon DD hh24:mi:ss TZR YYYY');

Upvotes: 2

Related Questions