Database Connector error:'22007:[Oracle][ODBC] Invalid datetime format.'

Hi.
I am trying to display data from oracle database, using Crystal Report XI.
The error come out everytime I try to display this datetime field. After through checking, I found that for table that have all datetime filled, no error. This error only come out if i use datetime field that only some having datetime value while the other record have no value. It is blank or null value. I try to set the option in reporting tab, change null value to default value but it'snot working. Pls advise what should i do further. Thanks for your kind attention and advise.
idahnoridahAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
Are you simply trying to display the date or are you grouping or filtering on it?

Are you using it in a formula?

mlmcc
0
idahnoridahAuthor Commented:
Hi. Tq for your response. I want to fisplay the date. Grouping or filtering is not done yet.

No. I am not using a formula. I try to create formula to solve this error but it doesn't work, maybe you can advise the best formula to make this work.
0
idahnoridahAuthor Commented:
sorry, typo error.. display, not fisplay. Tq
0
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

mlmccCommented:
It should just display and if NULL not show anything.

Sounds like you have some bad data in the database.

mlmcc
0
idahnoridahAuthor Commented:
From the error message, invalid datetime format, I guess the crystal report and odbc is now reading the format as datetime as it should be but for the null date, it is not following the expected format. Probably  you can help to advise how to work on this.. converting the null date to the required format. Tq for your help.
0
mlmccCommented:
If you set the option in the tab for the report, did you do that before or after you started the report.

There is a similar option in the FILE --> REPORT OPTIONS
Make sure it is set and try again.

If that doesn't do it then you will need to convert the NULL on the database side.

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.