String to Date - BAD DATE FORMAT STRING Error

[jwrjwr54]

I know this is simple, but can't make it work:

Using CR XI against an Oracle DB

{PROJECT_MV.CURRENT_PHASE_FINISH_DATE} is a String field formatted as '2010-10-31'. When I add Date or CDate (i.e., date({PROJECT_MV.CURRENT_PHASE_FINISH_DATE})), I get a Bad Date Format String error.

Thanks for any help.
W

 

[briangriffin]

Probably an easier way, but this is how I do it:

date(left({PROJECT_MV.CURRENT_PHASE_FINISH_DATE} ,4) + "," + mid({PROJECT_MV.CURRENT_PHASE_FINISH_DATE} ,6,2) + "," + right({PROJECT_MV.CURRENT_PHASE_FINISH_DATE} ,2))

If converting string dates is something you'll be doing often, consider compiling this as a custom function.

 

[jwrjwr54]

Still getting Bad Date Format Field. Your formula puts the string into a format that Crystal says can be changed to a Date Field. It should work and I'm stumped.
Thanks
Whit

 

[briangriffin]

When you remove the Date(), does it display

2010,10,06

?

I wonder if you have a bad record - try restricting the recordset to a single record and see if it works.

 

[jwrjwr54]

If I remove Date(), it does display 2010,10,06. I'll try one record, factor out nulls. Many thanks for all your help
Whit

 

[fisheromacse]

You can use a formula like below to check for null or invalid (too short/too long) date strings. You could also add a numeric check if you were concerned about non-numeric values being a possibility.

IF (
isnull({PROJECT_MV.CURRENT_PHASE_FINISH_DATE}) OR LEN({PROJECT_MV.CURRENT_PHASE_FINISH_DATE})<8 or LEN({PROJECT_MV.CURRENT_PHASE_FINISH_DATE})>10)
)
THEN Date(9999,01,01)
ELSE Date({PROJECT_MV.CURRENT_PHASE_FINISH_DATE})