Crystal Reports and Oracle Dates

[Former Member]

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

[Former Member]

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

[Former Member]

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.

[Former Member]

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

[Former Member]

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.