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

[Daniel Morgan]

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:
http://www.oracle.com/pls/db92/db92.docindex?remark=homepage

Oracle 9i SQL Reference - Format Models
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements4a.htm#34512


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:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomdatetimeformatstrings.asp

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,
>
> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
>
> 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
>