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

Question

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)

score 1 · Answer 1 · edited May 23 '17 at 12:13

1

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

But ODP.NET (developed by Oracle) utilises Oracle formats, not .NET formats. So "IW" should be somehow supported. I understand that it's impossible to convert to date with "IW" format, since it isn't pointing to any specific date, but my goal is to do something like `oracle.ToString("IW")` (with format given) just to obtain string representation of date in "IW" format (unfortunately there's no such method in ODP, so I tried to use `OracleGlobalization`). And the link with "allowed format strings" you provide, lists only "popular ones". My questions concerns all of them, not only popular ones. – Bru May 08 '14 at 08:03
And "IW" is just an example of unsupported formats. I mention how to identify them in original question. – Bru May 08 '14 at 08:07
Not really because the `OracleDate` structure is a representation of the Oracle `DATE` data type as retrieved from the database. You cannot save a date formatted with `IW` to a `DATE` field which is why it is output only. That's why this is tied to whatever is allowed in `NLS_DATE_FORMAT`. This isn't about .Net format strings but how Oracle implemented their date object. I know it's frustrating - ODP has caused me more headaches than I care to remember. Why not use the.Net equivalent? What's to stop you? – Steve Pettifer May 08 '14 at 08:19
I know all of that. I must stick to Oracle formats, since I get Oracle formats as an input to my application and I cannot change it. – Bru May 08 '14 at 08:38
So create a map of Oracle formats to .Net formats. I know it's clunky and a paint to set up (although thankfully it would be a one-time effort), but if you cannot use a database connection and cannot make use of Oracle formats, you're left with precious little choice. – Steve Pettifer May 08 '14 at 08:40
A map is not an option, since Oracle formats are only parts of possible format string given by user and this is crazy idea to cover all possible formats. I think that I should write my own interpreter and I really don't want to follow that path. – Bru May 08 '14 at 08:45
I suspect you'll find it no easier to write your own interpreter as you will still have to interpret every single possible format and then deal with it appropriately. Not only that, since you're using the globalization classes I'm guessing this has to deal with international variants too.That said, it is only the so-called output only formats you'd need to deal with as all others can be dealt with using '`OracleDate.ToString()`. – Steve Pettifer May 08 '14 at 08:50

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

1 Answers1

Further Reading