Support for IFormatProvider parameter in Convert.ToDateTime

crazypit · Post by **crazypit** » Tue 07 Dec 2010 13:14

Well, that is surely odd!

My session parameters are:

PARAMETER VALUE
1 NLS_LANGUAGE AMERICAN
2 NLS_TERRITORY AMERICA
3 NLS_CURRENCY $
4 NLS_ISO_CURRENCY AMERICA
5 NLS_NUMERIC_CHARACTERS .,
6 NLS_CALENDAR GREGORIAN
7 NLS_DATE_FORMAT DD-MON-RR
8 NLS_DATE_LANGUAGE AMERICAN
9 NLS_SORT BINARY
10 NLS_TIME_FORMAT HH.MI.SSXFF AM
11 NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
12 NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
13 NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
14 NLS_DUAL_CURRENCY $
15 NLS_COMP BINARY
16 NLS_LENGTH_SEMANTICS BYTE
17 NLS_NCHAR_CONV_EXCP FALSE

My database parameters are:

PARAMETER VALUE
1 NLS_LANGUAGE AMERICAN
2 NLS_TERRITORY AMERICA
3 NLS_CURRENCY $
4 NLS_ISO_CURRENCY AMERICA
5 NLS_NUMERIC_CHARACTERS .,
6 NLS_CHARACTERSET EL8ISO8859P7
7 NLS_CALENDAR GREGORIAN
8 NLS_DATE_FORMAT DD-MON-RR
9 NLS_DATE_LANGUAGE AMERICAN
10 NLS_SORT BINARY
11 NLS_TIME_FORMAT HH.MI.SSXFF AM
12 NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
13 NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
14 NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
15 NLS_DUAL_CURRENCY $
16 NLS_COMP BINARY
17 NLS_LENGTH_SEMANTICS BYTE
18 NLS_NCHAR_CONV_EXCP FALSE
19 NLS_NCHAR_CHARACTERSET AL16UTF16
20 NLS_RDBMS_VERSION 11.2.0.1.0

Essentially, only the Character Set is changed from the default configuration of the setup. It is changed to EL8ISO8859P7 which is the Greek ISO character set. Session parameters are never changed. Oracle Instant Client 11.2.0.1 is used, no Direct mode. DB is Oracle 11.2.0.1. No special parameters in the connection string are used except from the usual ones.

StanislavK · Post by **StanislavK** » Wed 08 Dec 2010 17:17

We couldn't reproduce such behaviour on a database with the EL8MSWIN1253 character set either. Could you please try executing these commands
a) under the Direct connection;
b) with a standard tool like SQL*Plus.
Please tell us about the results.

crazypit · Post by **crazypit** » Thu 09 Dec 2010 08:17

Now, it is getting really odd....

Running both queries through SQL* Plus tool of my Oracle Instant Client, BOTH queries are eligible.

I think, this needs to be solved with Oracle support.

In any case, using the Greek format meridian characters in a format string, seems rather off to me. I really think that the general format placeholder should be enough.

StanislavK · Post by **StanislavK** » Fri 10 Dec 2010 15:30

Did you try performing this query on other servers or from other workstations? If yes, what were the results?

In case you can determine what causes different behaviour of Oracle servers, we would be grateful if you describe us these configuration differences so that we are able to generate query with 'PM' or 'μμ' depending on them.

crazypit · Post by **crazypit** » Fri 10 Dec 2010 16:15

Using 3 different servers and 4-5 workstations, we get the same results. In fact, we never encountered the behavior you described in your tests.
The :

Code: Select all

select TO_DATE('24/2/2010 11:52:55 πμ', 'fmDDfm/fmMMfm/YYYY fmHH12fm:MI:SS AM') from dual

throws the ORA-01855 'AM/A.M. or PM/P.M. required' error because it cannot interpret the πμ /μμ in my date, as the default nls language date is the 'AMERICAN'. To be true, if i try :

Code: Select all

select TO_DATE('24/2/2010 11:52:55 AM', 'fmDDfm/fmMMfm/YYYY fmHH12fm:MI:SS AM') from dual

it works.

The nls_date_language=GREEK parameter, sets the default language for the database and then the πμ/μμ are immediately recognized and everything works.

I can't seem to find any reason for what you say you encounter in your tests. I would ask you to change the generated query accordingly and if someone else, from an other country maybe, questions this, then we discuss about it again. For now, it seems that we are the only ones that requested this feature in the first place, and still no one else in this forum commented otherwise.

StanislavK · Post by **StanislavK** » Wed 15 Dec 2010 15:04

We have analyzed the situation, the proper behaviour is the one you've described. We will change SQL generation for the Convert.ToDate function in such way that the SQL with 'PM' in the date format string is generated.

crazypit · Post by **crazypit** » Wed 05 Jan 2011 15:20

Hello,
Is this fixed in the latest 6.00.70 version?

StanislavK · Post by **StanislavK** » Thu 06 Jan 2011 12:57

The 6.0.70 build includes a single critical fix for the Direct connection mode:
http://www.devart.com/forums/viewtopic.php?t=19842

We will release the build with the fix for the Convert.ToDateTime method in several days, and inform you here when it is published.

StanislavK · Post by **StanislavK** » Wed 12 Jan 2011 17:16

We have released the 6.0.86 version of dotConnect for Oracle which includes the fix for the TO_DATE conversion problem. This build can be downloaded from
http://www.devart.com/dotconnect/oracle/download.html
(the trial version) or from Registered Users' Area (for users with active subscription only):
http://secure.devart.com/

For the detailed information about the fixes and improvements available in this build, please refer to
http://www.devart.com/forums/viewtopic.php?t=19968