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

[vsued]

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]

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();