Date in OQL with Oracle

[Hans-Georg]

A statement like
"Select * from OrderExtent p where p.orderDate<timestamp '2005-10-15 13:10:15' "

does not work with Oracle:
ERROR:
ORA-01830: date format picture ends before converting entire input string

Solution?

[Ralph Waldenmaier]

Hello Hans-Georg,

I tried to reproduce the described behavior, but the query you have shown worked well for me. Here is the SQL statement that was generated by OpenAccess:

SELECT a."id" COL1,
  a."customer_i_d" COL2,
  a."freight" COL3,
  a."id2" COL4,
  a."id3" COL5,
  a."id5" COL6,
  a."order_date" COL7,
  a."required_date" COL8,
  a."ship_address" COL9,
  a."ship_city" COL10,
  a."ship_country" COL11,
  a."ship_name" COL12,
  a."ship_postal_code" COL13,
  a."ship_region" COL14,
  a."shipped_date" COL15
FROM "VAORDER" a
WHERE a."order_date" < TO_TIMESTAMP('2005-10-15 13:10:15','YYYY-MM-DD HH24:MI:SS.FF');

Can you please try to execute the statement directly on your database, in order to see if you have the same problem here? Please adjust the table and column names, so that the statement matches your schema.

Can you also provide the statement that is generated by OpenAccess and the structure of your table? It would be helpful if you can send me a DDL Script, which I can use to generate the same database schema locally and reproduce the error.

We are looking forward to hearing from you.

Best wishes,
Ralph
the Telerik team

[Hans-Georg]

Hello Ralph,

thanks for your answer. There is no problem with a SQL statement like yours. But in our version of Vanatec OpenAccess (4.3.19 797) an OQL statement
SELECT * FROM ....  WHERE StartDate <= timestamp '2010-31-12 00:00:00'

is converted into the SQL statement

SELECT ... FROM .... WHERE start_date <= '2010-31-12 00:00:00'

and this produces the error.

[Ralph Waldenmaier]

Hello Hans-Georg,

In order to solve this problem I suggest you to include a DateTime DBParameter in your query instead of writing the values directly as string. Here is how the code looks like in my case:

IObjectScope scp = ctx.GetScope();
var query = "Select * from VAORDERExtent p where p.Order_date < $1";
IQuery iqry = scp.GetOqlQuery(query);
IQueryResult qryRes = iqry.Execute(new DateTime(2005,10,15));

I hope this is helpful.

Greetings,
Ralph
the Telerik team

[Thomas]

Hi Ralph,

we do have a similar problem with SQL-Server (german configuration).
"timestamp" being an OQL-keyword I'd expect correct transformation of the string-representation to a format compatible with the target database under all circumstances.

The problem exists in ORM - Vers. 09.04.0501

Did you fix this issue in subsequent ORM - versions ?

If not, are you going to fix it ?

Best regards
Thomas

[Thomas]

Hello Thomas,

yes, the current version of OpenAccess transforms
OQL timestamp '1967-03-29 01:02:03.120'
to SQL (T-SQL) CONVERT(DATETIME, '1967-03-29 01:02:03.120', 121)
which is a culture-independent format. Please note, that the X content of the timestamp 'X'  is not interpreted at the moment, jsut surrounded with the CONVERT. Otherwise we would need to figure out the culture in which the datetime was given.

Regards,
Thomas
the Telerik team

[Thomas]

Hello Thomas,

while sending the last message I noticed that you are using Oracle. For Oracle, we transform to
TO_TIMESTAMP('X'','YYYY-MM-DD HH24:MI:SS.FF')
Again, we do not interpret the X ourselves.

Kind regards,
Thomas
the Telerik team