Date in OQL with Oracle

Thread is closed for posting
7 posts, 0 answers
  1. Hans-Georg
    Hans-Georg avatar
    4 posts
    Member since:
    Sep 2008

    Posted 09 Jun 2011 Link to this post

    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?
  2.
  3. Ralph Waldenmaier
    Admin
    Ralph Waldenmaier avatar
    202 posts

    Posted 14 Jun 2011 Link to this post

    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
    Q1’11 SP1 of Telerik OpenAccess is available for download; also available is the Q2'11 Roadmap for Telerik OpenAccess ORM.
  5. Hans-Georg
    Hans-Georg avatar
    4 posts
    Member since:
    Sep 2008

    Posted 15 Jun 2011 Link to this post

    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.

  7. Ralph Waldenmaier
    Admin
    Ralph Waldenmaier avatar
    202 posts

    Posted 15 Jun 2011 Link to this post

    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
    Q1’11 SP1 of Telerik OpenAccess is available for download; also available is the Q2'11 Roadmap for Telerik OpenAccess ORM.
  9. Thomas
    Thomas avatar
    2 posts
    Member since:
    Jan 2008

    Posted 23 Nov 2012 Link to this post

    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

  11. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 26 Nov 2012 Link to this post

    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
    Telerik OpenAccess ORM Meets ASP.NET Web API. Read more.
  13. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 26 Nov 2012 Link to this post

    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
    Telerik OpenAccess ORM Meets ASP.NET Web API. Read more.
Back to Top