Lightspeed, Oracle TO_DATE function, LINQ

This may not be a Lightspeed question, but more a LINQ one, however, I'm hoping you might be able to offer some advice:

I am trying to generate a sql statement to be used in Oracle11g, using linq.

The problem arises when using dates:

The SQL generated by linq gives

SELECT *
FROM <table>
WHERE start_date > '24/11/2012 02:00:00' and end_date < '28/11/2012 14:00:00'

This causes an oracle error: ORA-01830 - date format picture ends before converting entire input string

Adding TO_DATE to the query fixes the ORA-01830, as it is converting the string to a oracle date whilst now knowing the date format.

SELECT *
FROM <table>
WHERE start_date > TO_DATE('24/11/2012 02:00:00','DD/MM/YYYY HH24:MI:SS') and end_date < TO_DATE('28/11/2012 14:00:00','DD/MM/YYYY HH24:MI:SS')

So, is there a way to add TO_DATE to LINQ (for oracle) or to combine lightspeed methods with LINQ to produce the required date time manipulation.

Update - Installed latest nightly build and still produces Oracle error as TO_DATE in not wrapped around the dates in string format.

Hi Douglas,

Are you passing the dates as strings or as DateTime instances? When we pass these down to ADO.NET they should be being passed through to Oracle as DateTime instances so there should be no conversion occuring at all. Are you determining the SQL statement by using the logging facility in LightSpeed? If so be careful because the SQL you see there is not always the SQL which is being sent to the server in that parameters are emitted inline where as they are passed as parameters by LightSpeed where the parameter values are in the native types where as what you see in the logging channel is the values converted to strings so values such as dates just use the default .ToString() format.

In regards to how you would handle TO_DATE though in case this is need either now or in the future, you would need to handle this via a custom function mapping - have a look at http://www.mindscapehq.com/documentation/lightspeed/Advanced-Querying-Techniques/Invoking-SQL-Functions for details about how to set this up. You would want to create a method called ToDate and then have the first argument as your date and the second as your format.

Jeremy