ORA-01843: not a valid month error in oracle while updating

Question

I want to update my date column with the format as dd-mm-yy. And in my database the column is of date datatype. So while Updating I am sending data as

05-12-2017 but getting error as

ORA-01843: not a valid month

in my stored procedure I am sending it as

HOTO_ACCEPTENCE_DATE = TO_DATE(PHOTO_ACCEPTENCE_DATE, 'dd-m-yy'),

My full procedure:

PROCEDURE UPD_WF_BY_FIBER_ENG
(
  PJOB_PROGRESS_ID IN TBL_FIBER_INV_JOB_PROGRESS.JOB_PROGRESS_ID%TYPE,
  PSTATUS_ID IN TBL_FIBER_INV_JOB_PROGRESS.STATUS_ID%TYPE,
  --PLIT_OFFERED_LENGTH IN TBL_FIBER_INV_JOB_PROGRESS.LIT_OFFERED_LENGTH%TYPE,
  PHOTO_ACTUAL_LENGTH IN TBL_FIBER_INV_JOB_PROGRESS.HOTO_ACTUAL_LENGTH%TYPE,
  PLIT_ACTUAL_LENGTH IN TBL_FIBER_INV_JOB_PROGRESS.LIT_ACTUAL_LENGTH%TYPE,

  PHOTO_ACCEPTENCE_DATE IN TBL_FIBER_INV_JOB_PROGRESS.HOTO_ACCEPTENCE_DATE%TYPE,
  PLIT_ACCEPTENCE_DATE IN TBL_FIBER_INV_JOB_PROGRESS.LIT_ACCEPTENCE_DATE%TYPE,

  PAPPROVED_BY IN TBL_FIBER_INV_JOB_PROGRESS.APPROVED_BY%TYPE,
  PREJECTED_BY IN TBL_FIBER_INV_JOB_PROGRESS.REJECTED_BY%TYPE,

  PAPPROV_REJECT_REMARK IN TBL_FIBER_INV_JOB_PROGRESS.APPROV_REJECT_REMARK%TYPE,

  PMODIFIED_BY IN TBL_FIBER_INV_JOB_PROGRESS.MODIFIED_BY%TYPE,
  PUMS_GROUP_ASS_BY_ID IN TBL_FIBER_INV_JOB_PROGRESS.UMS_GROUP_ASS_BY_ID%TYPE,
  PUMS_GROUP_ASS_BY_NAME IN TBL_FIBER_INV_JOB_PROGRESS.UMS_GROUP_ASS_BY_NAME%TYPE,
  PUMS_GROUP_ASS_TO_ID IN TBL_FIBER_INV_JOB_PROGRESS.UMS_GROUP_ASS_TO_ID%TYPE,
  PUMS_GROUP_ASS_TO_NAME IN TBL_FIBER_INV_JOB_PROGRESS.UMS_GROUP_ASS_TO_NAME%TYPE,
  PISABDMISSING IN TBL_FIBER_INV_JOB_PROGRESS.ISABDMISSING%TYPE,
  --PSPVENDORXML IN XMLTYPE,
  POUTMSG OUT NVARCHAR2
)
AS
VCNTSPVENCNT NUMBER :=0;
BEGIN

  UPDATE TBL_FIBER_INV_JOB_PROGRESS
    SET STATUS_ID = PSTATUS_ID,
       -- LIT_OFFERED_LENGTH = PLIT_OFFERED_LENGTH,
        HOTO_ACTUAL_LENGTH = PHOTO_ACTUAL_LENGTH,
        LIT_ACTUAL_LENGTH = PLIT_ACTUAL_LENGTH,
        HOTO_ACCEPTENCE_DATE = TO_DATE(PHOTO_ACCEPTENCE_DATE, 'DD-MM-YY'),
        LIT_ACCEPTENCE_DATE = TO_DATE(PLIT_ACCEPTENCE_DATE,  'DD-MM-YY'),
        APPROVED_BY = PAPPROVED_BY,
        APPROVED_DATE = DECODE(PAPPROVED_BY,NULL,NULL,SYSDATE),
        REJECTED_BY = PREJECTED_BY,
        REJECTED_DATE = DECODE(PREJECTED_BY,NULL,NULL,SYSDATE),
        APPROV_REJECT_REMARK = PAPPROV_REJECT_REMARK,
        MODIFIED_BY = PMODIFIED_BY,
        MODIFIED_DATE = SYSDATE,
        UMS_GROUP_ASS_BY_ID = PUMS_GROUP_ASS_BY_ID,
        UMS_GROUP_ASS_BY_NAME = PUMS_GROUP_ASS_BY_NAME,
        UMS_GROUP_ASS_TO_ID = PUMS_GROUP_ASS_TO_ID,
        UMS_GROUP_ASS_TO_NAME = PUMS_GROUP_ASS_TO_NAME,
        ISABDMISSING = PISABDMISSING
    WHERE JOB_PROGRESS_ID   = PJOB_PROGRESS_ID;

update

try
        {
            DBObject ObjDBObject = new DBObject(strConnectionString);
            string strProcedureName = strPackageName + ".UPD_WF_BY_FIBER_ENG";

            List<OracleParameter> lstParameters = new List<OracleParameter>();

            OracleParameter ObjOracleParameter = new OracleParameter("PJOB_PROGRESS_ID", FiberDataInsertion.PROG_ID);
            OracleParameter ObjOracleParameter1 = new OracleParameter("PSTATUS_ID", 1);
            OracleParameter ObjOracleParameter2 = new OracleParameter("PHOTO_ACTUAL_LENGTH", FiberDataInsertion.HOTO_ACTUAL_LENGTH);
            OracleParameter ObjOracleParameter3 = new OracleParameter("PLIT_ACTUAL_LENGTH", FiberDataInsertion.LIT_ACTUAL_LENGTH);
            OracleParameter ObjOracleParameter4 = new OracleParameter("PHOTO_ACCEPTENCE_DATE", FiberDataInsertion.HOTO_ACCP_DATE);
            OracleParameter ObjOracleParameter5 = new OracleParameter("PLIT_ACCEPTENCE_DATE", FiberDataInsertion.LIT_ACCP_DATE);
            OracleParameter ObjOracleParameter6 = new OracleParameter("PAPPROVED_BY", "NADEEM5.KHAN");
            OracleParameter ObjOracleParameter7 = new OracleParameter("PREJECTED_BY", "DB");
            OracleParameter ObjOracleParameter8 = new OracleParameter("PAPPROV_REJECT_REMARK", "this is test");
            OracleParameter ObjOracleParameter9 = new OracleParameter("PMODIFIED_BY", FiberDataInsertion.MODIFIED_BY);
            OracleParameter ObjOracleParameter10 = new OracleParameter("PUMS_GROUP_ASS_BY_ID", FiberDataInsertion.UMS_GROUP_ASS_BY_ID);
            OracleParameter ObjOracleParameter11 = new OracleParameter("PUMS_GROUP_ASS_BY_NAME", FiberDataInsertion.UMS_GROUP_ASS_BY_NAME);
            OracleParameter ObjOracleParameter12 = new OracleParameter("PUMS_GROUP_ASS_TO_ID", FiberDataInsertion.UMS_GROUP_ASS_TO_ID);
            OracleParameter ObjOracleParameter13 = new OracleParameter("PUMS_GROUP_ASS_TO_NAME", FiberDataInsertion.UMS_GROUP_ASS_TO_NAME);
            OracleParameter ObjOracleParameter14 = new OracleParameter("PISABDMISSING", FiberDataInsertion.MISS_ASBUILT);


            OracleParameter ObjOracleParameter15 = new OracleParameter
            {
                ParameterName = "POUTMSG",
                Size = 500,
                OracleDbType = OracleDbType.NVarchar2,
                Direction = ParameterDirection.Output
            };

            lstParameters.Add(ObjOracleParameter);
            lstParameters.Add(ObjOracleParameter1);
            lstParameters.Add(ObjOracleParameter2);
            lstParameters.Add(ObjOracleParameter3);
            lstParameters.Add(ObjOracleParameter4);
            lstParameters.Add(ObjOracleParameter5);
            lstParameters.Add(ObjOracleParameter6);
            lstParameters.Add(ObjOracleParameter7);
            lstParameters.Add(ObjOracleParameter8);
            lstParameters.Add(ObjOracleParameter9);
            lstParameters.Add(ObjOracleParameter10);
            lstParameters.Add(ObjOracleParameter11);
            lstParameters.Add(ObjOracleParameter12);
            lstParameters.Add(ObjOracleParameter13);
            lstParameters.Add(ObjOracleParameter14);
            lstParameters.Add(ObjOracleParameter15);


            strMessage = ObjDBObject.ExecuteNonQuery(strProcedureName, lstParameters);

        }
        catch (Exception)
        {                
            throw;
        }
        return strMessage;

Also the error details

at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck) at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck) at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery() at FiberInventoryPortal.Models.DAL.DBObject.ExecuteNonQuery(String strProcedureName, List1 lstParameters) in d:\Nadeem\FiberInventory_214\FiberInventoryPortal\FiberInventoryPortal\Models\DAL\DBObject.cs:line 114`

`'dd-m-yy'` is not a valid date format. Please post exactly what you are doing. [mcve] is best. — Mat, Dec 07 '17 at 10:11
Dates have an Oracle-specific internal representation, not a format like that. It looks like your're trying to update a date variable/column value to another date, but based on itself - though with that partial code (with chars missing) it's hard to be sure. Don't call `to_date()` for something that's already a date though... — Alex Poole, Dec 07 '17 at 10:11
@AlexPoole: without calling `to_date()` also its not working and giving the same error — Nad, Dec 07 '17 at 10:12
Then you're still doing an implicit conversion somewhere.Show a complete example that demonstrates the problem (not a mangled fragment), including how the variables etc. are declared. — Alex Poole, Dec 07 '17 at 10:13
@AlexPoole: See my procedure where I am calling my date function. that still doesnt works — Nad, Dec 07 '17 at 10:15
It might also be helpful to show exactly how you're calling the procedure, and the full error stack you get, not just that ORA-01843 message. — Alex Poole, Dec 07 '17 at 10:34

score 3 · Answer 1 · answered Dec 07 '17 at 10:20

HOTO_ACCEPTENCE_DATE = TO_DATE(PHOTO_ACCEPTENCE_DATE, 'DD-MM-YY')

TO_DATE( date_string, format_model, nls_params ) takes a string as the first argument so Oracle must implicitly convert a non-string argument to a string and is effectively doing:

HOTO_ACCEPTENCE_DATE = TO_DATE(
                         TO_CHAR(
                           PHOTO_ACCEPTENCE_DATE,
                           ( SELECT value 
                             FROM   NLS_SESSION_PARAMETERS
                             WHERE  parameter = 'NLS_DATE_FORMAT' )
                         ),
                         'DD-MM-YY'
                       )

If the NLS_DATE_FORMAT session parameter does not match DD-MM-YY then you will get an error. Since the NLS_DATE_FORMAT is a session parameter and can be changed by each user then relying on this is one way for your code to start raising exception as the users change their settings without your code ever changing.

What you want to do is just use:

HOTO_ACCEPTENCE_DATE = PHOTO_ACCEPTENCE_DATE,

As you do not need to convert a date to anything when you are storing it as a date.

I want to update my date column with the format as dd-mm-yy.

A date does not have a format - it is stored internally to the database as 7-bytes (representing year, month, day, hour, minute and second) and it is not until whatever user interface you are using (i.e. SQL/Plus, SQL Developer, Java, etc) tries to display it to you, the user, and converts it into something you would find meaningful (usually a string) that the date has a format.

tried removing that also, but still getting the same error as `not a valid month` — Nad, Dec 07 '17 at 10:26
@VVVV - are you sure it's still coming form the same place? You're doing the same thing with `PLIT_ACCEPTENCE_DATE`, which should also be a simple assignment. — Alex Poole, Dec 07 '17 at 10:28
@AlexPoole: while setting date, I am setting format in datepicker as `dd-mm-yy` from front end.that's it. even though I am getting the error — Nad, Dec 07 '17 at 10:29
@VVVV You have the same problem on this line `LIT_ACCEPTENCE_DATE = TO_DATE(PLIT_ACCEPTENCE_DATE, 'DD-MM-YY')` — MT0, Dec 07 '17 at 10:29
@VVVV Is it throwing an error on the SQL statement in the procedure or when you are invoking the procedure and trying to pass strings rather than dates into the procedure? — MT0, Dec 07 '17 at 10:32
@MT0: while executing the query, I am getting the error as not a valid month. — Nad, Dec 07 '17 at 10:33

score 0 · Answer 2 · answered Dec 07 '17 at 10:11

0

You can try using

HOTO_ACCEPTENCE_DATE = TO_DATE(PHOTO_ACCEPTENCE_DATE, 'dd-mm-yy'),

see the document for possible combinations

answered Dec 07 '17 at 10:11

Srini V

11,045
14
66
89

score 0 · Answer 3 · answered Dec 07 '17 at 10:12

0

Considering the format you posted, 'dd-m-yy', it seems that the month part is incorrect. It should be MM instead of M.

Ref.: https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm

answered Dec 07 '17 at 10:12

Bruno Medeiros

2,251
21
34

are you sure your input is in '05-12-2017' format, not in '12-05-2017' (american) format? That would also explain the problem. – Bruno Medeiros Dec 07 '17 at 10:18
1

@BrunoJCM - 12 and 5 are both valid months...? – Alex Poole Dec 07 '17 at 10:20
Sure they are, but I understand that this specific date is just a sample of the real input and other instances may be getting into the procedure and causing the error. – Bruno Medeiros Dec 07 '17 at 10:22

ORA-01843: not a valid month error in oracle while updating

3 Answers3