[Mono-list] Problem with datetime format and oracle...

Daniel Morgan danielmorgan@verizon.net
Sat, 19 Feb 2005 19:14:45 -0500

I told you a fib.  Oracle's default date format is 'DD-MON-RR' which is 
"dd-MMM-yy" for System.DataTime.  I know this works since I successfully 
inserted a row using an OracleParameter.

Also, here is a list of books for Oracle 9i at Oracle Technology 
Network.  You need to be registered to use OTN.  If you are not 
registered (its free), I strongly suggest you do.

List of Oracle 9i books on OTN:

Oracle 9i SQL Reference - Format Models

In .NET 1.1 Framework Help, Search for "format specifiers, date and time 
format strings" and choose Custom DateTime Format Strings.
Or on the web at MSDN Library:

Right now, I am working on converting the Oracle Date format string to 
Mono's System.DateTime format string and vice-versa.  This code will end 
up in OracleDateTime as internal static functions.  I will be using 
OCINlsGetInfo since it works for me.   OCINlsGetInfo will be located in 
OracleConnection as an internal function.  I will try to commit to svn a 
working version (not complete) tonight.

Daniel Morgan wrote:

> The default date format for Oracle is 'DD-MMM-YY'.  Example: '13-FEB-02'
> The fix needs to be able to handle situations where the date format 
> could be changed via ALTER SESSION SET NLS_DATE_FORMAT, such as,
> I have some ideas on how to solve it:
> 1. OCINlsGetInfo can be used to get the date format.  However, once I 
> got this, I would need to reformat the date from OracleDateTime or 
> DateTime to Oracle's date and vice-vesa.
> 2. (I haven't tried this yet - create DllImport's for OCI functions 
> OCIDateTimeToText and OCIDateTimeFromText and calls these functions 
> with an explicit
>    date format string.  Oracle's OCIDate can stay as an IntPtr and use 
> other OCI functions to deal with it. 3. There are even OCI functions 
> to put the OCIDate into a byte array or get the OCIDate from a byte 
> array.   OCIDateTimeFromArray and OCIDateTimeToArray.  DllImport's are 
> needed for these functions too.
> More info can be found in Oracle Call Interface Programmer's Guide.
> http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96584/oci18m36.htm#512147 
> **
> Here is working code so far...
>        private string GetSessionNlsDateFormat()
>        {
>            byte[] buffer = new Byte[64];
>            uint bufflen = (uint) buffer.Length;
>            ushort item = (ushort) OciNlsServiceType.DATEFORMAT;
>            IntPtr session = connection.Session;
>            int st = OciCalls.OCINlsGetInfo (session, 
> connection.ErrorHandle,
>                ref buffer, bufflen, item);
>            // Get length of returned string
>            int     rsize = 0;
>            IntPtr    env = connection.Environment;
>            OciCalls.OCICharSetToUnicode (env, null, buffer, out rsize);
>                      // Get string
>            StringBuilder ret = new StringBuilder(rsize);
>            OciCalls.OCICharSetToUnicode (env, ret, buffer, out rsize);
>            string nlsDateFormat = ret.ToString ();
>            return nlsDateFormat;
>        }
>        internal static int OCINlsGetInfo (IntPtr hndl,
>            IntPtr errhp,
>            ref byte[] bufp,
>            uint buflen,
>            ushort item)
>        {
>            Trace.WriteLineIf(traceOci, "OCINlsGetInfo", "OCI");
>            return OciNativeCalls.OCINlsGetInfo (hndl, errhp, bufp, 
> buflen, item);
>        }
>            [DllImport ("oci")]
>            internal static extern int OCINlsGetInfo (IntPtr hndl,
>                IntPtr errhp,
>                [In][Out] byte[] bufp,
>                uint buflen,
>                ushort item);
> Hubert FONGARNAND wrote:
>> I use mono to deal with an oracle database...
>> the date format of my oracle database is "DD/MM/YY"
>> but mono always send date like "DD/MM/YYYY"
>> my NLS_LANG parameter is : NLS_LANG="AMERICAN_AMERICA.WE8ISO8859P1"
> _______________________________________________
> Mono-list maillist  -  Mono-list@lists.ximian.com
> http://lists.ximian.com/mailman/listinfo/mono-list