CODEWITHSUNDEEP
c#oracle-databaseodp.netdate-formattingodac
Bru
Bru

Reputation: 514

ODP.NET: Oracle date formatting to "output-only" formats

I'd like to use Oracle date formatting in C#, such as in to_char(date, format), without database connection nor writing my own format interpreter.

Sample application (I'm using Oracle Data Provider for .NET):

namespace OracleDateToCharTest
{
    using System;
    using Oracle.DataAccess.Client;
    using Oracle.DataAccess.Types;

    class Program
    {

        /// <param name="fmt"> Oracle-like format string. </param>
        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
        }
    }
}

It works well with formats like "mm-dd-yyyy", "mm-dd-yyyy HH24:Mi:ss", but unfortunately it doesn't work with "output-only" formats like "IW" (output-only formats are formats that you can specify in TO*_DATETIME funcitons according to Table 9-4 on http://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_commands_1029.htm).

When I call for example

OracleDateToChar(DateTime.Now, "IW")

I get ORA-01820 format code cannot appear in date input format exception in ToString() line.

I'd understand if I got this error in ToDate() method, but in ToString() it seems to be a bug in ODP.NET.

Question: Is there a way to implement OracleDateToChar method that can handle output-only formats? (assuming that calling select to_char(:date, :fmt) from nvl; from oracle database is not an option)

Upvotes: 2

Views: 2288

Answers (1)

Steve Pettifer
Steve Pettifer

Reputation: 2043

The point about that Oracle documentation is that it applies to DATE_FORMAT which is part of the Oracle language. Therefore you can only execute it as a PL/SQL statement which is why you cannot get it to work client side: IW is not a valid format mask for ODP.Net globalization as far as I know. In fact the ODP globalization DateFormat defaults to whatever you have in NLS_DATE_FORMAT locally which is a full date format string, whereas IW is for getting the week of the year rather than for formatting a date itself. As far as I know, nothing in the .Net framework will recognise that Oracle-specific string, so in order for it to work you would need to issue a command to Oracle and get the result back, as per the example on that page:

CONSIDER mydatetime
DATE_FORMAT MON-RRRR-DD-HH24

That seems like a lot of overkill to change a date format. There are some good alternative siggestions which are client-side .Net in this SO answer

Further Reading

The Oracle docs for the Oracle Date Structure (OracleDate) ToString() method notes that

The returned value is a string representation of the OracleDate in the format specified by the thread's OracleGlobalization.DateFormat property

(My emphasis). The definition for this can be found here. A useful list of allowed format strings for NLS_DATE_FORMAT, and therefore by extension OracleDate.ToString, can be found here.

Upvotes: 1

Related Questions