Converting YYYYMMDDHHMM time stamp to mm/dd/yyyy Crystal XI

[chefty1]

I am currently using Crystal XI and am running into a problem trying to convert a field. One of my fields displays a date/time stamp. It currently displays in the YYYMMDDHHMM format. We are trying to automate this report to pull yesterdays information.

We currently run it manually using the "IS LIKE" funtion and the search the date as 20120116*. This pulls in yesterdays information.

Does anyone know how I can convert this field so I can use the currentdate -1 functionality?

Below is our SQL query info from Crystal.


SELECT "BI"."PTNAME", "BI"."PAT_NUM", "BI"."NRS_STATION", "BI"."BED_ABBREV", "PHM_POE_ORDER"."TYPE", "PHM_POE_ORDER"."ENTERED_BY", "PHM_POE_ORDER"."MSG_DATE_TIME", "PHM_POE_FREETEXT"."ORDER_TEXT", "PHM_POE_FREETEXT"."TEXT_SEQ"
FROM {oj ("SQLUser"."PHM_POE_ORDER" "PHM_POE_ORDER" INNER JOIN "SQLUser"."PHM_POE_FREETEXT" "PHM_POE_FREETEXT" ON ("PHM_POE_ORDER"."ITN"="PHM_POE_FREETEXT"."ITN") AND ("PHM_POE_ORDER"."POE_ORDER_NUMBER"="PHM_POE_FREETEXT"."POE_ORDER_NUMBER")) RIGHT OUTER JOIN "SQLUser"."BI" "BI" ON "PHM_POE_ORDER"."ITN"="BI"."ITN"}
WHERE "PHM_POE_ORDER"."TYPE"='FRE' AND "PHM_POE_FREETEXT"."TEXT_SEQ"='2' AND "PHM_POE_ORDER"."MSG_DATE_TIME" LIKE '20120116%'

Thanks for any help/guidance you can give me.

 

[lbass]

Create a formula {@cdate} like this:

stringvar x := {PHM_POE_ORDER.MSG_DATE_TIME};
date(val(left(x,4)),val(mid(x,5,2)),val(mid(x,7,2)))

Then use a record selection formula like this:

{@cdate}=currentdate-1

-LB

 

[chefty1]

Thanks!! That worked!