title: Inserting time section in date field using oracle
body: <p>I have a procedure in oracle:</p>
  
  <pre><code>CREATE OR REPLACE 
  PROCEDURE ONE
      (
      p_GUID IN ONE.GUID%TYPE,
      p_START_DATE IN ONE.START_DATE%TYPE,
      p_END_DATE IN ONE.END_DATE%TYPE,
      p_RETURN OUT INTEGER
      )
  AS
  BEGIN
      INSERT INTO ONETABLE (GUID, START_DATE, END_DATE)
    VALUES (p_GUID, to_date(p_START_DATE, 'YYYY-MM-DD HH24:MI:SS'), to_date(p_END_DATE, 'YYYY-MM-DD HH24:MI:SS'));
      COMMIT;
    EXCEPTION
          WHEN OTHERS THEN
          p_RETURN:= 1;
  END;
  </code></pre>
  
  <p>edit, because people seem to saying to try this (this does not work either, i tried to use to_date (above example) to try and fix the problem):</p>
  
  <pre><code>CREATE OR REPLACE 
  PROCEDURE ONE
      (
      p_GUID IN ONE.GUID%TYPE,
      p_START_DATE IN ONE.START_DATE%TYPE,
      p_END_DATE IN ONE.END_DATE%TYPE,
      p_RETURN OUT INTEGER
      )
  AS
  BEGIN
      INSERT INTO ONETABLE (GUID, START_DATE, END_DATE)
    VALUES (p_GUID, p_START_DATE, p_END_DATE);
      COMMIT;
    EXCEPTION
          WHEN OTHERS THEN
          p_RETURN:= 1;
  END;
  </code></pre>
  
  <p>When call it from c# (with DateTime.NOW for example) - it fails.</p>
  
  <p>It only seems to work if the start date and end date are in the format of 05-apr-2013. but how do I get the time in there as well as in the input?</p>
  
  <p>If I replace</p>
  
  <pre><code>p_START_DATE, 'YYYY-MM-DD HH24:MI:SS'
  </code></pre>
  
  <p>With</p>
  
  <pre><code>'2013-05-13 12:13:14', 'YYYY-MM-DD HH24:MI:SS'
  </code></pre>
  
  <p>This works, but upon calling the procedure from c# it fails.</p>
  
  <p>Also If I manually run the procedure from db client I still have to input 05-apr-2013 and only that works. I can not input the time as it fails if I try so.</p>
  
  <p>Start date and end date are DATE type in oracle db.</p>
  
  <p>--</p>
  
  <p>Depending what I try I get different messages.</p>
  
  <p>As I investigated, it seems to have something to do with this:</p>
  
  <pre><code>SELECT value FROM v$nls_parameters WHERE parameter ='NLS_DATE_FORMAT';
  </code></pre>
  
  <p>Which returns:</p>
  
  <pre><code>DD-MON-RR
  </code></pre>
  
  <p>And I cannot change that as am not the db owner.</p>
  
  <p>--</p>
  
  <p><strong>so how to get the time part in there?</strong></p>
  
  <p>Here is the complete SQL:</p>
  
  <pre><code>select parameter, value from nls_session_parameters;
  </code></pre>
  
  <p>Which returns:</p>
  
  <pre><code>NLS_LANGUAGE    AMERICAN
  NLS_TERRITORY   AMERICA
  NLS_CURRENCY    $
  NLS_ISO_CURRENCY    AMERICA
  NLS_NUMERIC_CHARACTERS  . 
  NLS_CALENDAR    GREGORIAN
  NLS_DATE_FORMAT DD-MON-RR
  NLS_DATE_LANGUAGE   AMERICAN
  NLS_SORT    BINARY
  NLS_TIME_FORMAT HH.MI.SSXFF AM
  NLS_TIMESTAMP_FORMAT    DD-MON-RR HH.MI.SSXFF AM
  NLS_TIME_TZ_FORMAT  HH.MI.SSXFF AM TZR
  NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
  NLS_DUAL_CURRENCY   $
  NLS_COMP    BINARY
  NLS_LENGTH_SEMANTICS    BYTE
  NLS_NCHAR_CONV_EXCP FALSE
  </code></pre>