? Convert String CCYYMM to date

[Crazycritter1]

Hello -

I have a date field CCYYMM that is read as a string within Crystal 10. My goal is to be able to use the date range of LastFullMonth to filter data. I have tried various formulas - trimming the data, reversing, and adding info, so that I currently have a string formula that presents the data as MM/DD/CCYY. However, further attempts to convert this string into usable data for use in the LastFullMonth range has not worked. Everything I've tried has returned a "string is required here" when referencing the LastFullMonth, or has simply returned a "bad date..." error.

I would greatly appreciate any insights/assistance you can provide, as I am still fairly new at Crystal... getting better as time goes on.. but still a newbie. And a frustrated one at the moment. If you need additional info, please let me know.

(-: ;-)

Thanks in Advance!

 

[synapsevampire]

Try creating a formula of:

//{@MyDateFormula}
if len({table.date}) = 10 then
cdate(val(mid({table.date},7)),val(left({table.date},2)),val(mid({table.date},4,2)))
else
cdate(1970,1,1)

So I default to 1/1/1970 when it is not a valid date.

Then in the record selection reference the formula as in:

{@MyDateFormula} in lastfullmonth

-k

 

[Crazycritter1]

Thanks!

I tried it, but it returned ALL results as 1/1/1970... I'm going to scrap my current report and start new, so I am sure that my previous attempts aren't messing the results up.

I'll write back in a bit, and let you know how it went. I appreciate your help!

K.

 

[synapsevampire]

Previous attempts have nothing to do with this.

Try posting an example of what is in the field, my formula checks for a length of 10 characters, and the result you're getting states that it is NOT 10 characters as your original post stated.

The problem is that you gave us bad information, not the formula or previous attempts.

-k

 

[Crazycritter1]

Ok, the resulting string from the formulas I spoke of is 11/01/2006, however, when I substituted my formula that produces that result, with your suggestion, the dates that were returned were all 01/01/1970. Likewise, I tried it with my original data CCYYMM... and got the same result.

I'd like to get rid of doing the multiple formula thing I have going... is there anything that can utilize the current CCYYMM data?

Again, I appreciate your help. By the time I saw your response, I had already deleted the original report I'd been working on.

 

[synapsevampire]

Try this directly against the field in the database:

//{@MyDateFormula}
if len({table.date}) = 6 then
cdate(val(left({table.date},4)),val(mid({table.date},5)),1)
else
cdate(1970,1,1)

-k