Skip to Content
author's profile photo Former Member
Former Member

Crystal Reports and Oracle Dates

Post Author: Robert

CA Forum: General

Hey Guys, I am experiencing some issues with Crystal Reports Developer XI (SP4) and Oracle Dates. Situation:I have a view setup in oracle database, i use this view to base my reports. Within the view there is a logtime column (to_date(logtime,'DD-MM-YYYY') & to_date(logtime,'DD-MON-YYYY') -: i have used both) When i connect to the database the database expert (view on the left of the screen) displays the logtime column as date time, but when i go to refersh the report with data with the logtime as group 1, i get a whole lot of errors:1: Failed to Retrieve data from Database2: Failed to Retrieve data from Database Details: ORA-01861: Literal does not match format String Database vendor code: 18613: Failed to Retrieve data from Database Details: Failed to execute SQL statement. OCI Call................................... Anybody got any ideas why this is happening, if i use the to_char the report work fine...well it will not sort by date!! Any help, most appreciated.... Robert

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Sep 12, 2007 at 05:31 PM

    Post Author: foghat

    CA Forum: General

    If you are receiving actual oracle errors, I'm not sure the problem is with crystal. Sounds like there is something wrong with your oracle syntax. check this link out:http://www.techonthenet.com/oracle/errors/ora01861.php What happens when you run the crystal sql directly against your database? I'm guessing you will get the same error.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 13, 2007 at 03:56 PM

    Post Author: Robert

    CA Forum: General

    Hey, Thanks for the reply....

    Actually when i run the query in Sql Developer/SQLPLUS it works fine 😊

    And when i use the to_char(...) it work fine in Crystal Developer, only when i use the to_date() !!

    Crazy Error, thanks for the input

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 13, 2007 at 06:06 PM

    Post Author: foghat

    CA Forum: General

    Well, while not the most elegant solution, if you can't resolve the error, you could try do your to_char to get the query running and then create a forumla in crystal to covnert the string back to a date and then sort on that field. F Do a search in crystal help for Cdate or DateValue.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 14, 2007 at 04:57 PM

    Post Author: Robert

    CA Forum: General

    Hello there and thanks for your reply,

    I have found the problem, it was not really a problem but a slight misjudgment of the use of the to_date function in oracle!

    I should have used to_date in conjunction with to_char 😊

    to_date(to_char(logtime,'DD-MM-YYYY'),'DD-MM-YYYY')

    Presto

    No need fo any formula in crystal to do this....

    Thanks again

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.