How to pass DateTime parameter in SQL query for Oracle DB

Question

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

possible duplicate of How to pass datetime from c# to sql correctly? — Liam
– Liam, Commented Apr 22, 2014 at 9:07
You need to use SqlDbType.DateTime, don't use ToString() — Liam
– Liam, Commented Apr 22, 2014 at 9:08
Actually scratch that, if your using a timestamp, you shouldn't pass a datetime in at all. Timestamp stamps the time. I.e. SQL add's this for you. — Liam
– Liam, Commented Apr 22, 2014 at 9:10

Wernfried Domscheit · Accepted Answer · 2014-04-22 09:55:36Z

3

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

edited Apr 22, 2014 at 9:55

answered Apr 22, 2014 at 9:20

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Florent Gz · Accepted Answer · 2014-04-22 09:22:59Z

0

Try using OracleParameter

OracleParameter para = new OracleParameter();
para.ParameterName = ":createdPrior";
para.Direction = ParameterDirection.Input;
para.DbType = DbType.DateTime;
para.Value = value;

command.Parameters.Add(para);

answered Apr 22, 2014 at 9:22

Florent Gz

9341 gold badge11 silver badges22 bronze badges

1 Comment

Wernfried Domscheit Over a year ago

When you use the Oracle Data Provider (what you should do) you have to use OracleDbType.TimeStamp instead of DbType.DateTime

Collectives™ on Stack Overflow

How to pass DateTime parameter in SQL query for Oracle DB

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related