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

[Daniel Morgan]

The Oracle dictionary view NLS_SESSION_PARAMETERS is helpful too.  I 
know this available on Oracle 9i, but I don't know if it's available on 
Oracle 8i.  Oracle 8i docs are not available on OTN because its not 
supported by Oracle anymore.

SELECT VALUE
FROM NLS_SESSION_PARAMETERS
WHERE PARAMETER = 'NLS_DATE_FORMAT';

Daniel Morgan wrote:

> 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"
>>>