[Mono-list] Problem with datetime format and oracle...
Sat, 19 Feb 2005 20:49:19 -0500
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.
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:
> 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,
>> 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.
>> Here is working code so far...
>> private string GetSessionNlsDateFormat()
>> byte buffer = new Byte;
>> uint bufflen = (uint) buffer.Length;
>> ushort item = (ushort) OciNlsServiceType.DATEFORMAT;
>> IntPtr session = connection.Session;
>> int st = OciCalls.OCINlsGetInfo (session,
>> 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"