String to Date Formula

[acsmith525]

I have a flat file, with a 6 character field I need to convert to be in mm/dd/yy format. Can someone please advise?
thanks!

 

[synapsevampire]

You might want to share what format it is currently in.

However this is how to do so for MMDDYY, assuming it is not a numeric field and it is always 6 characters:

cdate(mid({table.datefield},5,2),mid({table.datefield},3,2),left({table.datefield},2))

-k

 

[acsmith525]

Thanks so much for your reply!! I tried it, and got the Crystal message "too many arguments have been given to this function". The file is an ODBC flat text file. The database is fixed width, the field is character (they all are) e.g. {xpress_clinic_txt.discharge}. Crystal Reports 7.5.

Trying the straight formula
cdate({xpress.clinic.txt.discharge})says "no errors" in the formula test mode, but when the report is run, the message "bad date format string" appears.

This is how I wrote your recommendation if you'd like to verify I understood correctly:

cdate(mid{xpress_clinic_txt.discharge},5,2),mid ({xpress_clinic_txt.discharge}},3,2),left({xpress_clinic_txt.discharge},2))

All help is very much appreciated!

 

[Thadeus]

Will this work for you?

NumberVar MontPart;
NumberVar DayPart;
NumberVar YearPart;

//Extract Month
MontPart := Val(Left({Sheet1_.Date},2));
//Extract Day
DayPart := Val(Mid({Sheet1_.Date},3,2));
//Extract Year
YearPart := Val(Right({Sheet1_.Date},2));

Date(YearPart,MontPart,DayPart)

Could all be done in one line with no variables, but this makes it easy to understand what's going on.

Thadeus

 

[acsmith525]

Thadeus - almost there! the characters 060603, returns the value 6/6/3 with the formula you just gave me. Should I specify mm/dd/yy format somehow?

Thank you SO much!

 

[synapsevampire]

You had a couple of typos.

Note that you didn't explain how the data was stored, just how you wanted it output. Databases store things differently, for fast responses here, provide example data and expected output, text descriptions help a bit, but not nearly as much as seeing what you actually have.

Try:
cdate(
left({xpress_clinic_txt.discharge},2),
mid({xpress_clinic_txt.discharge},3,2),
mid ({xpress_clinic_txt.discharge},5,2),
)

Once you have the date output, right click it, select format field and format the display however you like.

Hope that does it.

-k

 

[Thadeus]

Synapse, That doesn't work for me. cDate requires a single string argument, or three numeric arguments. (CR9)

acsSmith, Synapse is right about right clicking on the formula field and setting a custom format for the date.

He's also right about the amount of detail (example data, etc) to attempt to provide.

Have a good one!
Thadeus

 

[acsmith525]

Well guys, I appreciate your help. The last formula Synapse gave me provided the "No Errors" message, but again, when the report runs the formula encounters the nonconforming string value: "Bad date format string". I did however use a variation with a positive result:

stringVar strDate := ({pganey_hospa_txt.DISCHARGE DATE});
date(tonumber(strDate[1 to 2]),tonumber(strDate[3 to 4]),tonumber(strDate[5 to 6]));
strdate[1 to 2] + "/" + strdate[3 to 4] + "/" + strdate [5 to 6]

So all is well. Regarding providing how the data was stored with my info, I apologize, I didn't mean to give you heartburn. I am new at this and am not sure what you mean. I thought I had provided the necessary info. I tried to format the question similar to those I found in other requests. Thanks.

 

[synapsevampire]

Soory, it should have been:

Try:
cdate(
val(left({xpress_clinic_txt.discharge},2)),
val(mid({xpress_clinic_txt.discharge},3,2)),
val(mid ({xpress_clinic_txt.discharge},5,2)),
)

-k