Support for IFormatProvider parameter in Convert.ToDateTime

Discussion of open issues, suggestions and bugs regarding LinqConnect – Devart's LINQ to SQL compatible ORM
crazypit
Posts: 163
Joined: Wed 15 Apr 2009 08:43

Support for IFormatProvider parameter in Convert.ToDateTime

Post by crazypit » Tue 23 Mar 2010 15:26

Hello,

I use the latest stable version of dotConnect for Oracle.

When i use the Convert.ToDateTime in a LINQ query, the correct TO_DATE function is created in the generated SQL. Nevertheless, if i try to use the overloaded Convert.ToDateTime method that accepts a cultureinfo object, i get an exception. I guess, there is not relevant support.

Isn't there a way to convert the standard General pattern of the DateTimeFormat represented by the CultureInfo in order to automatically set the correct Oracle format mask for the generated TO_DATE function? Correct me if i'm wrong, but it doesn't seem too hard to play with the characters...

For now, i use the SetSessionInfo of the connection to set the DateFormat. Nevertheless, this is a string and in Oracle format, so there is not a way for me to derive it from a user-defined culture...

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Wed 24 Mar 2010 17:59

We are investigating the situation.
I will let you know about the results.

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Tue 27 Apr 2010 14:21

We have changed the behaviour, DateFormat will be set in accordance with Session parameters in the nearest build.

crazypit
Posts: 163
Joined: Wed 15 Apr 2009 08:43

Post by crazypit » Tue 27 Apr 2010 15:20

Can you elaborate more on that please?

How does this solve my case?

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Wed 28 Apr 2010 15:39

The Convert.ToDateTime with IFormatProvider will be translated into the TO_DATE call with the format string obtained from the passed CultureInfo object.

crazypit
Posts: 163
Joined: Wed 15 Apr 2009 08:43

Post by crazypit » Thu 29 Apr 2010 07:14

Exactly what i wanted!

Thank you!

crazypit
Posts: 163
Joined: Wed 15 Apr 2009 08:43

Post by crazypit » Fri 02 Jul 2010 10:43

Sorry that i forgot:

The same translation should be applied when using the Convert.ToDecimal Method (String, IFormatProvider). In fact all the relevant Convert.To[XXXXX] (String, IFormatProvider) .NET number conversion methods should generate proper TO_NUMBER SQL functions with the correct NLS parameters.

Is this also fixed?

crazypit
Posts: 163
Joined: Wed 15 Apr 2009 08:43

Post by crazypit » Mon 05 Jul 2010 09:09

Furthermore, there is a problem with the TO_DATE translation you performed and the AM, PM indicator. For example the following SQL query is generated for a string representation of a date in Greek:

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

'πμ' is the Greek indicator for 'AM'. Nevertheless, this throws an error. The way i have found to overcome this, is to generate a query like this:

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

I really don't know if there is another, more elegant way. But the thing is, that your implementation of IFormatProvider parameter in Convert.ToDateTime does not translate well when the string contains a local AM PM indicator.

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Wed 07 Jul 2010 11:11

We will investigate the possibility to add the functionality you have requested.
As a workaround try to adjust your Oracle client instance (set the NLS_DATE_LANGUAGE and other parameters in registry to the requierd values, this should help).
In case you are using Direct mode then you should adjust the Session info after connecting. Cast dataContext.Connection to OracleConnection and call the SetSessionInfo() method. Don't forget to adjust the OracleGlobalization instance to contain correct NLS parameters' values.

crazypit
Posts: 163
Joined: Wed 15 Apr 2009 08:43

Post by crazypit » Wed 07 Jul 2010 11:48

Thanks about the workarounds but i already know about them. They put dependencies on the type of DB server used and i really need to be able to work with all of the supported types of LinqConnect without changing code or bloating it with multiple IF statements.

I will be waiting for the proper implementation towards a complete db-agnostic Linq To SQL solution as you promised in the relevant roadmap.

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Mon 09 Aug 2010 11:52

We have implemented the functionality you have requested.
It will be available in the nearest build.
Unfortunately, we don't have any timeframe for it.

crazypit
Posts: 163
Joined: Wed 15 Apr 2009 08:43

Post by crazypit » Wed 01 Dec 2010 14:41

Hello,

I never tested the functionality until now. Using the latest 6.00.58 version i still get an error. The generated query is like this:

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

where the Greek AM PM indicator is used in the format string! This is wrong as 'πμ' or 'μμ' are not valid Oracle format characters. You should generate something like this:

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

where the AM or PM format character is used along with the nls lang parameter.

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Thu 02 Dec 2010 17:18

As I can understand, a query like

Code: Select all

select TO_DATE('24/2/2010 11:52:55 πμ'), 'fmDDfm/fmMMfm/YYYY fmHH12fm:MI:SS πμ', 'NLS_DATE_LANGUAGE = GREEK') from dual
is correct, whereas using 'AM' in the format string should cause an Oracle error. I've tried the Convert.ToDateTime method with the latest 6.0.58 version, and a format string with 'πμ' was used in the generated SQL.

Could you please specify the following:
- the version of the Oracle server you are working with;
- the exact LINQ query with which you are ancountering the problem;
- the message and stack trace of the exception you are getting.

If possible, please send us a complete test project with which the problem can be reproduced.

crazypit
Posts: 163
Joined: Wed 15 Apr 2009 08:43

Post by crazypit » Fri 03 Dec 2010 07:47

The exact opposite is correct. A Format string with "AM, PM" should be generated from the LINQ expression and not the Greek equivalent "πμ, μμ". If i run the query :

Code: Select all

select TO_DATE('24/2/2010 11:52:55 πμ', 'fmDDfm/fmMMfm/YYYY fmHH12fm:MI:SS πμ', 'NLS_DATE_LANGUAGE = GREEK') from dual
in my Oracle 11.2.0.1 system , i get a :
ORA-01821: date format not recognized

If i run a :

Code: Select all

select TO_DATE('24/2/2010 11:52:55 πμ', 'fmDDfm/fmMMfm/YYYY fmHH12fm:MI:SS PM', 'NLS_DATE_LANGUAGE = GREEK') from dual;
everything works as expected. In fact, it sones not matter at all if "AM" or "PM" is used. The correct string representation is returned according to the "πμ" defined in the date. The "AM", "PM" is just a placeholder which says that the supplied date does not end at the Second but also has the PM, AM indicator.

So, it is not a matter of LINQ exception. It justs generates wrong SQL.

Quoting from the relevant Oracle documentation:

TO_DATE(, [ format_mask ], [ nls_language ])
string1 is the string that will be converted to a date.

The format_mask parameter is optional. It is the format that will be used to convert string1 to a date.

nls_language is optional. The nls_language parameter sets the default language of the database. This language is used for messages, day and month names, symbols for AD, BC, a.m., and p.m., and the default sorting mechanism. This parameter also determines the default values of the parameters NLS_DATE_LANGUAGE and NLS_SORT.

The following table shows options for the format_mask parameter. These parameters can be used in various combinations.

Parameter Explanation
YEAR Year, spelled out alphabetically
YYYY 4-digit year
YYY
YY
Y Last 3, 2, or 1 digit(s) of year.
IYY
IY
I Last 3, 2, or 1 digit(s) of ISO year.
IYYY 4-digit year based on the ISO standard
RRRR Accepts a 2-digit year and returns a 4-digit year.
A value between 0-49 will return a 20xx year.
A value between 50-99 will return a 19xx year.
Q Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM Month (01-12; JAN = 01).
MON Abbreviated name of the month.
MONTH The name of month, padded with blanks to length of 9 characters.
RM Roman numeral month (I-XII; JAN = I).
WW The week of the year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W The week of the month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW The week of year (1-52 or 1-53) based on the ISO standard.
D Day of the week (1-7). Sunday is day 1 when nls_territory is set to 'AMERICA' but differs if another nls_territory is set (i.e. 'UNITED KINGDOM' or 'GERMANY' - in these cases Monday is 1.
DAY Name of the day.
DD The day of month (1-31).
DDD The day of year (1-366).
DY Abbreviated name of the day. (Mon, Tue, Wed, etc)
J Julian day; the number of days since January 1, 4712 BC.
HH Hour of day (1-12).
HH12 Hour of day (1-12).
HH24 Hour of day (0-23).
MI Minute (0-59).
SS Second (0-59).
SSSSS Number of seconds past midnight (0-86399).
FF Fractional seconds. Use a value from 1 to 9 after FF to indicate the number of digits in the fractional seconds. For example, 'FF5'.
AM, A.M., PM, or P.M. Meridian indicator
AD or A.D AD indicator
BC or B.C. BC indicator
TZD Daylight savings identifier. For example, 'PST'
TZH Time zone hour.
TZM Time zone minute.
TZR Time zone region.

So, the Meridian indicator and all the format strings, are predefined! No greek representations are valid!

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Tue 07 Dec 2010 12:38

In our environment, the first example (with 'πμ' in the format string) works, and the second causes the ORA-01855 'AM/A.M. or PM/P.M. required' error. Could you please specify the following:
- whether you are connecting with the Oracle client or in the Direct mode; in the first case, please specify the exact client version;
- the NLS parameters of the database and session (please use the 'select * from nls_database_parameters' and 'select * from 'nls_database_parameters' queries for this);
- the connection string you are using (especially the parameters not related to connectivity itself, like 'Unicode').

Post Reply