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.

 

[Madawc]

This really belongs in one of the Crsytal Reports forums, probably

http://www.tek-tips.com/threadminder.cfm?pid=767.

Please do that next time.

As for your dates, the best way is to convert them. SOmething like

Date({PHM_POE_ORDER.MSG_DATE_TIME})

DIsplay first with unselected data and confirm it is correct.

ToText is the best way to convert a date back to string data, but I doubt you need it.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP