INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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.

 

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

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

CODE

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.

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close