ORA-01843 not a valid month - Simple SQL with DateTime.

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
vsued
Posts: 1
Joined: Fri 07 Jun 2019 11:13

ORA-01843 not a valid month - Simple SQL with DateTime.

Post by vsued » Fri 07 Jun 2019 14:26

Hello hi all,
Iam try execute my SQL: sample class with SQL.

Code: Select all

        public List<Item> GetAllItens(int id, DateTime dateIni, DateTime dateEnd)
        {
            try
            {
                string dateIn= dateIni.ToString("dd/MM/yyyy");
                string dateEn = dateEnd.ToString("dd/MM/yyyy");
                var sql = $@"SELECT * 
                                FROM ITEM 
                              WHERE ID = {id}
                              AND TRUNC(DATEMOV) BETWEEN '{dateIn}' AND '{dateEn}'";

                return SqlQuery<Item>(sql);

            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
After execute, my command.Read return error:

Devart.Data.Oracle.OracleException (0x80004005): ORA-01843 not a valid month
Whats is this ???
My format date is the Brazilian

Shalex
Site Admin
Posts: 8660
Joined: Thu 14 Aug 2008 12:44

Re: ORA-01843 not a valid month - Simple SQL with DateTime.

Post by Shalex » Wed 12 Jun 2019 16:19

Please use parameters instead of plain SQL :

Code: Select all

    var p1 = new OracleParameter("p1", OracleDbType.TimeStampTZ);
    p1.Value = new DateTime(1980, 1, 1);

    var p2 = new OracleParameter("p2", OracleDbType.TimeStampTZ);
    p2.Value = DateTime.Now;

    var parameters = new OracleParameter[] { p1, p2 };
            
    var result = context.EMPs.SqlQuery("SELECT * FROM EMP where HIREDATE BETWEEN :p1 AND :p2", parameters).ToList();

Post Reply