Date Conversion 1

[gkrenton]

Using Crystal 9 and haven't done much calculations w/dates previously. However I have a 'string' field for a date in a YYYYMM format. I ultimately need to translate this into a date field to export the data to seperate software program as well as do 'date' calculations.
I'm stumped as to where to begin. Looking at previous post(s) most deal with YYYYMMDD conversions & seems that crystal prefers that layout better than this concantated version.

Thanks in advance for any tips.

Gina

 

[synapsevampire]

Well YYYYMM isn't a date, merely a month.

You can make an assumed date by using:

cdate("1/"+mid("200304",5,2)+"/"+left("200304",4))

Replacing the "200304" with your string field.

This would place the dates at the 1st of each month.

-k

 

[gkrenton]

Thanks though I'm still stuck. First guess my field is a number not a string but I can work around that by converting the number to a string via cstr which works fine. However -

Unfortunately this statement makes the month 01 consistently. so it would seem logical that the '1' is misplaced. Yet if I move the 1 to change the formula to

gcdate(mid("200304",5,2)+"/"+left("200304",4))

I get the result of 4/1/2003 however when I input my field name
where 200304 resides I get a bad date format string.
cdate(mid({@OTBDate},5,2)+"/"+left(@otbdate,4)) & the error box shows a result of 30/2003 - I 'think' the issue is that the 'format' includes a comma within the field ie 200,305.

Finally tried using the following statement but STILL get the bad date format error -
cdate(right({@OTBDate},2)+"/"+"01/"+left({@OTBDate},5))
Where the error box is saying 05/01/200,3. How do I get rid of this 'comma' that seems to be messing me up.

Thanks

Gina

 

[lbass]

If you use cstr, then be sure to format the number, as in this formula {@yearmonth}:

cstr({table.number},0,"&quot //where {table.number} is a number like 200304 (yyyyMM}

Then the following should work to convert it to a date where the day is always 01:

cdate(val(left({@yearmonth},4)),val(right({@yearmonth},2)),01)

-LB

 

[gkrenton]

That's the Ticket! Thanks a lot -

gina