How to parse the date passed as a odp.net parameter value?

Question

I am using Oracle Managed Data Access Client for .net. I need to pass my timestamp(format : MM/dd/yyyy hh:mi:ss:ff AM) from the parameter and also have to convert it into oracle specific timestamp using TIME_STAMP() function. If I directly inject the values, it works. But if I pass through the parameter, I get an error. I believe, it takes the parameter as an object rather than varchar/string.

So, how can I pass my values as an OracleParameter and convert it to oracle specific timeStamp.

But this wont. This will display the data for the date but not for correct timestamp specified (I even need to compare fraction of seconds).

string SELECTGROUPSESSIONS = @"SELECT * FROM (
                                                SELECT Recent.sent_date, Recent.thread_id, Recent.body_string, Recent.body_text, 
                                                Recent.message_string, Recent.message_text, Recent.body_len, Recent.from_jid , 
                                                Recent.to_jid, Recent.history_flag 
                                                FROM JM Recent Left Join 
                                                (Select * from JM where sent_date > TO_TIMESTAMP(:FromHistory,'MM/dd/yyyy hh:mi:ss:ff AM')) Old 
                                                on (Recent.body_string=Old.body_string and 
                                                Recent.body_len=Old.body_len and Recent.from_jid=Old.from_jid and 
                                                REGEXP_REPLACE( Recent.to_jid , '([/])\w+', '') = REGEXP_REPLACE( Old.to_jid , '([/])\w+', '') 
                                                and Recent.history_flag=Old.history_flag and Old.sent_date < Recent.sent_date) 
                                                where Recent.msg_type ='g' 
                                                and Recent.body_len>0 and Recent.sent_date > TO_TIMESTAMP(:FromDate,'MM/dd/yyyy hh:mi:ss:ff AM')  
                                                and Recent.sent_date <  TO_TIMESTAMP(:ToDate,'MM/dd/yyyy hh:mi:ss:ff AM')
                                                and Old.sent_date is null 
                                                order by Recent.sent_date asc  
                                                ) 
                                                WHERE rownum  <= {0}";

For the values:

My query should look like.

SELECT * FROM ( 
                        SELECT Recent.sent_date, Recent.thread_id, Recent.body_string, 
                        Recent.body_text, Recent.message_string, Recent.message_text, Recent.body_len, 
                        Recent.from_jid , Recent.to_jid, Recent.history_flag FROM JM Recent Left Join 
                        (Select * from JM where sent_date > 
                        TO_TIMESTAMP('02/19/2017 10:43:00:8357400 AM','MM/dd/yyyy hh:mi:ss:ff AM') ) Old on 
                        (Recent.body_string=Old.body_string and Recent.body_len=Old.body_len and 
                        Recent.from_jid=Old.from_jid and 
                        REGEXP_REPLACE( Recent.to_jid , '([/])\w+', '') = REGEXP_REPLACE( Old.to_jid , '([/])\w+', '') 
                        and Recent.history_flag=Old.history_flag and Old.sent_date < Recent.sent_date) 
                        where Recent.msg_type ='g' and Recent.body_len>0 and Recent.sent_date > 
                        TO_TIMESTAMP('03/21/2017 10:43:00:8357400 AM','MM/dd/yyyy hh:mi:ss:ff AM') and 
                        Recent.sent_date < TO_TIMESTAMP('03/22/2017 09:02:28:3049506 AM','MM/dd/yyyy hh:mi:ss:ff AM') 
                        and Old.sent_date is null order by Recent.sent_date asc  ) WHERE rownum <= 500 

C# Codes :

selectCommand = _factory.GetDbCommand(queryStatement, SqlConnection);

            selectCommand.Parameters.Clear();
            using (selectCommand)
            {
                if (parameters != null)
                {
                    foreach (var param in parameters)
                    {
                        selectCommand.Parameters.Add(_factory.CreateParameter(param.Key, param.Value));                           

                    }
                }

                try
                {                    

                    using (
                        var reader = selectCommand.ExecuteReader())
                    {
                        processReader(reader);
                    }

Result: Could it be due to mismatch of the date formats ?

Answer 1

You should try to add the parameter values directly, i.e. instead of

string SELECTGROUPSESSIONS = "SELECT ...
... sent_date > TO_TIMESTAMP(:FromHistory,'MM/dd/yyyy hh:mi:ss:ff AM')

try

string SELECTGROUPSESSIONS = "SELECT ...
... sent_date > :FromHistory ..."

selectCommand.Parameters.Add("FromHistory", OracleDbType.TimeStamp, ParameterDirection.Input).Value = {the C# DateTime value};