Passing C# datetime value to Oracle DB Query
<p>I have a SQL query that I am passing a C# variable into my Oracle DB.</p>

<p>I am having trouble passing a C# datetime variable, "PROCESS_DATE", into my query in my application.  I do not get any records back.  If I copy the query into my oracle developer tool, TOAD, it works fine and I get multiple records.</p>

<p>Here is the query I am using in my application:</p>

<pre><code>String SelectAllSQL = "SELECT * FROM REALMS_AUDIT.R2_GROUP_QUERY_RPT WHERE PROCESS_DATE = :pPROCESS_DATE";
</code></pre>

<p>I also tried converting the datetime variable into a shortDateString() so it matches the database exactly I then used the TO_DATE function, which I have to use if I query dates directly in TOAD, without any luck.  The shortDateString() changes my date into: <code>1/16/2016</code>, which is what I need, but the OracleDataReader does not like it.  Here it the query with the TO_DATE function:</p>

<pre><code>String SelectAllSQL = "SELECT * FROM REALMS_AUDIT.R2_GROUP_QUERY_RPT WHERE PROCESS_DATE = TO_DATE(:pPROCESS_DATE, 'MM-DD-YYYY'";
</code></pre>

<p><code>:pROCESS_DATE</code> is a datetime variable that is passed in.</p>

<p>There must be a breakdown between C# and Oracle in relation to handling a datetime variable.  <em>I am using Oracle DataReader</em> to handle the processing of the query.</p>

<pre><code>OracleDataReader dataReader = mDataAccess.SelectSqlRows ( oracleConnection, oracleCommand, sqlCommand, parameters );

        while ( dataReader.Read ( ) )
        {
                groupEntityFacilityRptList.Add ( ReadRecord ( dataReader ) );
        }
</code></pre>

<p>If I use the TO_DATE function, the application will not step into the while loop.  If I use the original query, it does but returns no data.</p>

<p><strong>The datetime variable PROCESSDATE looks like this:</strong></p>

<pre><code>1/16/2016 12:00:00 AM
</code></pre>

<p>I notice it has a timestamp on it, so I'm not sure if that is the problem or not.<br>
<strong>The data in Oracle is like this:</strong></p>

<pre><code>1/16/2016
</code></pre>