How to pass DateTime parameter in SQL query for Oracle DB
13,708
Do this one:
SELECT * FROM PERSON WHERE PSN_CREATED_DATE >= TO_TIMESTAMP(:createdPrior, 'yyyyMMddHH24missffff')
or use TimeStamp paramater in C#, should be like this
OracleParameter para = new OracleParameter(":createdPrior", OracleDbType.TimeStamp, ParameterDirection.Input);
para.Value = (Oracle.DataAccess.Types.OracleTimeStamp)value;
command.Parameters.Add(para);
Author by
Eugene
Updated on June 04, 2022Comments
-
Eugene almost 2 years
I need to include some 'TIMESTAMP' fields in SQL-query WHERE clause:
SELECT * FROM PERSON WHERE PSN_CREATED_DATE >= :createdPrior
In my code, createdPrior parameter is defined in the following way
... command.Parameters.Add(":createdPrior", Miscellaneous.convertToOracleTimeStamp(createdPrior)); ... static class Miscellaneous { public static OracleTimeStamp convertToOracleTimeStamp(DateTime dateTime) { OracleTimeStamp result = new OracleTimeStamp(dateTime); return result; } }
And as a result I receive a following exception
Exception: Additional information: ORA-00932: inconsistent datatypes: expected TIMESTAMP got NUMBER
Could you tell me, how to pass DateTime in SQL-query for Oracle DB?
P.S. What I've tried: -I've created an Oracle Parameter with characteristics
OracleParameter para = new OracleParameter(); para.ParameterName = ":createdPrior"; para.Direction = System.Data.ParameterDirection.Input; para.OracleDbType = OracleDbType.TimeStamp; para.Value = Miscellaneous.convertToOracleTimeStamp(createdPrior);
-I've passed just DateTime. And I've received another exception
-I've tried to convert parameter to DATE type (using to_date() method), but in this case it seems that I'm loosing minutes and seconds