Generating an Oracle TO_STRING call with custom formatting?

Generating an Oracle TO_STRING call with custom formatting?

Postby sheikdev » Fri 11 Mar 2011 17:42

Hi,
I'm working on converting the code of an app that connects to an ORACLE database using ODAC, with new code suing linqConnect.

I need to be able to generate in a linq select statement the equivalent to
Code: Select all
TO_CHAR(table.someDateTimeField,'HH24:MI:SS.FF')


That is, to get the time portion of the table field.

Or in general, be able to pass my own formatting string. I know the Convert.ToString() method with an IFormatProvider parameter is supported... but it uses whatever format it wants... or so it seems

I need something like this to be translated into SQL by linqConnect:

Code: Select all
var query = from item in dataContext.SomeTable
            select new {
                   FormattedDate = item.SomeDateField.ToString("HH:mm:ss")
            }


Again, in general, I need a way to genereate SQL code that uses TO_DATE, TO_CHAR, TO_NUMBER, etc, with my own custom format, or a way to pass an IFormatProvider to the Convert.To*() methods that gets translated into SQL that uses MY own custom format...

Thanks in advance,

Adrián
sheikdev
 
Posts: 2
Joined: Fri 11 Mar 2011 16:47
Location: Argentina

Postby StanislavK » Mon 14 Mar 2011 16:35

Could you please describe the problem you've encountered with the Convert.ToString() method in more details? E.g., please specify
1) the type of data being retrieved;
2) the arguments of the method;
3) the output of this method.

You should be able to work with the data retrieved as with common .NET objects. For example, you can use the methods of the Convert class and the DateTime structure, as well as the String.Format method. In particular, the LINQ query you've specified should return time portions of dates converted to strings (provided that 'SomeDateField' is of the DateTime type). Please tell us if you are encountering some other behaviour.

However, it is not guaranteed that this conversion is performed at the server side. For example, the above query is translated to something like "select SomeDateField from SomeTable", and the results are converted to strings at the client side.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby sheikdev » Mon 14 Mar 2011 19:03

Could you please describe the problem you've encountered with the Convert.ToString() method in more details? E.g., please specify
1) the type of data being retrieved;
2) the arguments of the method;
3) the output of this method.


I have two places where I need to convert DateTime or TimeStamp fields to strings:
Issue A)
Some select statements where I need a string representation of a date, truncating either the date part or the time part. ie, using Linq to generate a resulting SQL like the following (SomeTimeStamp is of type TimeStamp):

Code: Select all
SELECT TO_CHAR(T.SomeTimeStamp, 'HH24:MI:SS.FF') AS Time
FROM SomeTable T


But when using Convert.ToString(value, IFormatProvider) in the Linq query expression:

Code: Select all
var formatProvider = CultureInfo.InvariantCulture.DateTimeFormat
var query = from item in context.SomeTable
            select new {Time = Convert.ToString(item.SomeTimeStamp, formatProvider)}


That generates:

Code: Select all
SELECT TO_CHAR(T.SomeTimeStamp, ' 'fmMMfm/fmDDfm/YYYY HH24:MI:SS') AS Time
FROM SomeTable T


Notice three issues there:
- It uses a format string that includes both the date and the time part
- The time part does not include milliseconds
- I don't care about it for now, it notice the 'fm' string that is sorrounding the month and day characters (fmMMfm and fmDDfm instead of MM and DD).

So how do I call Convert.ToString(...) to make it generate the TO_CHAR(...) call with my own custom format, as in the first example?

I could do somehing like that by creating a new culture and modifying some private properties by reflection (a reaaaally nasty thing to do...):

Code: Select all
var formatProvider = new CultureInfo("en-US", true).DateTimeFormat;
formatProvider.LongTimePattern = "HH:mm:ss.fff";
formatProvider.ShortDatePattern= "";

formatProvider.GetType().GetField("generalLongTimePattern", BindingFlags.NonPublic | BindingFlags.Instance).SetValue(formatProvider, formatProvider.LongTimePattern);

var query = from item in context.SomeTable
            select new
            {
                HOUR_PART = Convert.ToString(item.SomeTimeStamp, formatProvider),
            };


With that I got the following (somewhat similar to what i needed):
Code: Select all
SELECT TO_CHAR(t1.TRADE_TIME, ' HH24:MI:SS.FF3') AS C1
FROM DEVSMRT2.REG_TRADE_VIEW t1


But also notice the extra space at the left of "HH24". It seems obvious that linqConnect uses the two properties I hacked in order to create the format string, and adds the space to separate whatever the date format string is from the time format....

So how do I make linqConnect to generate what I want? The same is also valid for any oracle TO_*(....) function.

I know, I know, if I'm gonna use a date, number, timestamp, etc, in the select statement, I probably can modify whatever legacy code in the app I'm refactoring, so it uses .Net types, and work around the formating some other way... But that is not the case of the following issue, where I need the formatted field value as part of a WHERE statement:

Issue B
There's a query that needs to filter rows from a Sales table within an interval of dates that includes several days , but only for certain time span during each day. The field I'm filtering by is a timestamp field.

So, the resuling query should be something like: retrieve all sales done from 03/01/2011 to 03/15/2011 that took place between 10:00AM and 11:00AM.

So the SQL generated by linqConnect should be something like:
Code: Select all
SELECT * FROM Sales S
WHERE S.Date BETWEEN '2011-03-01' AND '2011-03-15'
      AND TO_CHAR(S.Date, 'HH24:MI:SS.FF') BETWEEN '10:00:00.000' AND '11:00:00.000'           


As far as I know linqConnect cannot generate the BETWEEN operator, but it does not matter, 'cause it does generate the equivalent using '>=' and '<=' operators.
The problem is generating the TO_CHAR(...) call, like in Issue 1.

You should be able to work with the data retrieved as with common .NET objects. For example, you can use the methods of the Convert class and the DateTime structure, as well as the String.Format method. In particular, the LINQ query you've specified should return time portions of dates converted to strings (provided that 'SomeDateField' is of the DateTime type). Please tell us if you are encountering some other behaviour.


If I use String.Format(...) static method, or String.ToString("hh:mm:ss.fff") instance method applied to the field within the linq query expression, like:
Code: Select all
var query = from item in dataContext.SomeTable
            select new {
                   FormattedDate = item.SomeTimeStampField.ToString("HH:mm:ss")
            }


well, linqConnect seems to ignore the above use of ToString() (the same for String.Format), generating a SQL string that simply retrieves the field as it is, with no TO_CHAR call:

Code: Select all
SELECT t.SomeTimeStampField
FROM SomeTable t


I used a SELECT statement in this example, but it's the same for WHERE statements.

Maybe the conversion is made later, in memory, as you seem to imply in the following quoted paragraph:

However, it is not guaranteed that this conversion is performed at the server side. For example, the above query is translated to something like "select SomeDateField from SomeTable", and the results are converted to strings at the client side.



...but for the case of WHERE statements, even if being possible, I cannot perform the filter in memory: we're dealing with literally millions of records, so the SQL statement submitted to the DB must be complete (i.e., include the TO_CHAR(...) call).

Is what I need clear enough?
Thanks in advance :)
sheikdev
 
Posts: 2
Joined: Fri 11 Mar 2011 16:47
Location: Argentina

Postby StanislavK » Tue 15 Mar 2011 12:01

The correct way of getting the time part of the DateTime structure is to use the TimeOfDay property, like
Code: Select all
var query = from item in dataContext.SomeTable
            select new { Time = item.SomeTimeStampField.TimeOfDay };

In particular, queries like
Code: Select all
from item in dataContext.SomeTable
where item.SomeTimeStampField.TimeOfDay > dateTime1.TimeOfDay &&
  item.SomeTimeStampField.TimeOfDay < dateTime2.TimeOfDay
select item;

worked correctly in our environment.

As for format providers, you can pass your own IFormatProvider implementations to the methods of the Convert class. However, the problem with dates is that the whole date/time information should be converted. I.e., the format provider is used to format the date as '[date in its format] [time in its format]', thus generating a preceding space if an empty date format is set. For example,
Code: Select all
string str = Convert.ToString(DateTime.Now, formatProvider);

will produce a string with a preceding space as well for the provider defined as
Code: Select all
var formatProvider = new CultureInfo("en-US", true).DateTimeFormat;
formatProvider.LongTimePattern = "HH:mm:ss.fff";
formatProvider.ShortDatePattern = "";

Please tell us if this helps.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to LinqConnect (LINQ to SQL support)