title: ODP.NET: Oracle date formatting to "output-only" formats
body: <p>I'd like to use Oracle date formatting in C#, such as in <code>to_char(date, format)</code>, without database connection nor writing my own format interpreter.</p>
  
  <p>Sample application (I'm using Oracle Data Provider for .NET):</p>
  
  <pre><code>namespace OracleDateToCharTest
  {
      using System;
      using Oracle.DataAccess.Client;
      using Oracle.DataAccess.Types;
  
      class Program
      {
  
          /// &lt;param name="fmt"&gt; Oracle-like format string. &lt;/param&gt;
          public static string OracleDateToChar(DateTime date, string fmt)
          {
              //// preparing format for ToString()
              OracleGlobalization og = OracleGlobalization.GetThreadInfo();
              string prevFormat = og.DateFormat;
              og.DateFormat = fmt;
  
              try
              {
                  //// converting to OracleDate
                  OracleDate odacDate = new OracleDate(date); 
                  //// setting format for ToString()
                  OracleGlobalization.SetThreadInfo(og);
                  return odacDate.ToString();
              }
              catch (OracleTypeException ex)
              {
                  if (ex.Number == 1820)
                  {
                      //// described issue
                  }
                  throw;
              }
              finally
              {
                  og.DateFormat = prevFormat;
                  OracleGlobalization.SetThreadInfo(og);
              }
          }
  
          static void Main(string[] args)
          {
              var x = OracleDateToChar(DateTime.Now, "mm-dd-yyyy");
              var y = OracleDateToChar(DateTime.Now, "mm-dd-yyyy HH24:Mi:ss");
              var z = OracleDateToChar(DateTime.Now, "IW"); //// exception
          }
      }
  }
  </code></pre>
  
  <p>It works well with formats like <code>"mm-dd-yyyy"</code>, <code>"mm-dd-yyyy HH24:Mi:ss"</code>, but unfortunately it doesn't work with "output-only" formats like <code>"IW"</code> (<em>output-only formats</em> are formats that you can specify in TO*_DATETIME funcitons according to Table 9-4 on <a href="http://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_commands_1029.htm" rel="nofollow">http://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_commands_1029.htm</a>).</p>
  
  <p>When I call for example</p>
  
  <pre><code>OracleDateToChar(DateTime.Now, "IW")
  </code></pre>
  
  <p>I get <code>ORA-01820 format code cannot appear in date input format</code> exception in <code>ToString()</code> line.</p>
  
  <p>I'd understand if I got this error in <code>ToDate()</code> method, but in <code>ToString()</code> it seems to be a bug in ODP.NET.</p>
  
  <p><strong>Question:</strong> Is there a way to implement <code>OracleDateToChar</code> method that can handle output-only formats? (assuming that calling <code>select to_char(:date, :fmt) from nvl;</code> from oracle database is not an option)</p>