DateTime format wrong C# , Oracle

Question

In my app I capture a "Timestamp". This timestamp I later use when I call a stored procedure. At the moment I'm getting the error :

ORA-01830: date format picture ends before converting entire input string ORA-06512: at line 2

I need hour,min and sec because the column in the table must be unique.

Here is how i get my datetime:

private void getDate()
    {
        conn.Open();
        string query;
        query = "select to_char(sysdate, 'dd/mon/yyyy hh24:mi:ss') as CurrentTime from dual";
        OracleCommand cmd = new OracleCommand(query, conn);
        OracleDataReader dr = cmd.ExecuteReader();
        dr.Read();
        text = dr[0].ToString();
        dr.Close();
        conn.Close();
    }

This is how I call the procedure:

        OracleCommand cmd = new OracleCommand();
        cmd.Connection = conn;
        conn.Open();
        OracleTransaction trans = conn.BeginTransaction();
        cmd.CommandTimeout = 0;
        cmd.CommandText = "dc.hhrcv_insert_intrnl_audit_scn";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("pn_pallet_id", OracleDbType.Number).Value = palletid;
        cmd.Parameters.Add("pn_emp_id_no", OracleDbType.Number).Value = empid;
        cmd.Parameters.Add("pd_intrnl_audit_scan_datetime", OracleDbType.VarChar).Value = text;
        cmd.Parameters.Add("pn_company_id_no", OracleDbType.VarChar).Value = companyIdNo2;
        cmd.Parameters.Add("pn_order_no", OracleDbType.Number).Value = orderNo2;
        cmd.Parameters.Add("pn_carton_code", OracleDbType.Number).Value = carton_Code2;
        cmd.Parameters.Add("pn_no_of_full_carton", OracleDbType.Number).Value = txtNoOfCartons.Text;
        cmd.Parameters.Add("pn_no_of_packs", OracleDbType.Number).Value = txtNoOfPacks.Text;
        cmd.Parameters.Add(new OracleParameter("pv_error", OracleDbType.VarChar));
        cmd.Parameters["pv_error"].Direction = ParameterDirection.Output;
        string pv_error;
        cmd.ExecuteNonQuery();
        pv_error = cmd.Parameters["pv_error"].Value.ToString();
        if (pv_error.ToString() == "")
        {
            trans.Commit();
            frmMsgAudit ms = new frmMsgAudit(empid,palletid,orderno,text);
            ms.Show();
            this.Hide();
        }
        else
        {
            trans.Rollback();
            MessageBox.Show("" + pv_error, "Error");
        }
        conn.Close();

Getting the error on:

cmd.ExecuteNonQuery();

ORA-01830: date format picture ends before converting entire input string ORA-06512: at line 2

Thanks in advance.

What is the datatype of the parameter in the stredprocedure ? a varchar2 ? — A.B.Cade
– A.B.Cade, Commented Mar 14, 2013 at 6:44
is the error coming from your procedure? if so, please post your procedure code too. actually, if you are using oracle 11.0.2.3 or later, i suggest you to use entity framework. — Ray Cheng
– Ray Cheng, Commented Mar 14, 2013 at 6:46

Werner van den Heever · Accepted Answer · 2013-03-14 06:55:36Z

Thanks for all the quick responses!

grrr.. I really need to sit and walk through everything step by step! Anyways, this was the issue:

In stored procedure i had:

...
begin
insert into dc_internal_audit_scan (pallet_id_no,
                                    internal_audit_scan_emp,
                                    internal_audit_scan_datetime,
                                    company_id_no,
                                    order_no,
                                    carton_code,
                                    no_of_full_cartons,
                                    no_of_packs,
                                    last_update_datetime,
                                    username)      
                            values (ln_pallet_id_no,
                                    pn_emp_id_no,
                                    **pd_intrnl_audit_scan_datetime,**
                                    pn_company_id_no,
                                    pn_order_no,
                                    pv_carton_code,
                                    pn_no_of_full_cartons,
                                    pn_no_of_packs,
                                    sysdate,
                                    lv_emp_username);
end;

now:

...
begin
insert into dc_internal_audit_scan (pallet_id_no,
                                    internal_audit_scan_emp,
                                    internal_audit_scan_datetime,
                                    company_id_no,
                                    order_no,
                                    carton_code,
                                    no_of_full_cartons,
                                    no_of_packs,
                                    last_update_datetime,
                                    username)      
                            values (ln_pallet_id_no,
                                    pn_emp_id_no,
                                    **TO_DATE(pd_intrnl_audit_scan_datetime,'dd/mon/yyyy hh24:mi:ss'),**
                                    pn_company_id_no,
                                    pn_order_no,
                                    pv_carton_code,
                                    pn_no_of_full_cartons,
                                    pn_no_of_packs,
                                    sysdate,
                                    lv_emp_username);
end;

TO_DATE(pd_intrnl_audit_scan_datetime,'dd/mon/yyyy hh24:mi:ss')

thanks

I thought that was the issue - oracle implicitly converts strings to date according to NLS_DATE_FORAMT. Here is a demo (I made it already so why not add it...)

Community · Accepted Answer · 2017-05-23 12:04:54Z

1

~~Looks like this line is wrong;~~

~~query = "select to_char(sysdate, 'dd/mon/yyyy hh24:mi:ss')~~

~~What 24 doing here?~~

Try with;

query = "select to_char(sysdate, 'dd/mon/yyyy hh:mi:ss AM')

FROM ORA-01830 Error

You tried to enter a date value, but the date entered did not match the date format.

EDIT: Since A.B.Cade warned me, hh24 is a valid oracle format, but still I believe your sysdate's format and 'dd/mon/yyyy hh24:mi:ss' are different formats.

edited May 23, 2017 at 12:04

CommunityBot

11 silver badge

answered Mar 14, 2013 at 6:48

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

Collectives™ on Stack Overflow

DateTime format wrong C# , Oracle

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related