Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Formatting dates in Crystal Dictionaries

Posted on 2002-04-28
21
Medium Priority
?
84 Views
Last Modified: 2012-06-08
I am creating a crystal dictionary to use in some reports. But what I am finding is that if I use a date/time field in my dictionary and try to use it in my report it is formatted as 2002/04/02 08:06:00:00   If I try to format it in the report it does not seem to be recognized as a date field and only gives me text formatting features. How do I go about creating a dictionary that has dates formatted in the way that I want, or how do I correctly format a date in a report created from a dictionary? I was wondering if I needed  to create the dictionary entry using a formula, is so can somebody give me the formula to get 04/04/02 20:06 (military time) out of the above date?

Also I don’t know if this makes a difference but this is being used in a VB application with the Report Runtime Designer.
0
Comment
Question by:DavidNPD
  • 13
  • 8
21 Comments
 

Author Comment

by:DavidNPD
ID: 6978293
Those are helpfull, but I am still looking for the actual Formula for converting the dates from the long string to the short version I mentioned above. After looking at the articles it seems that using a formula will be the only way.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 6979286
Do all datetimes look like your example?  How can you tell AM from PM?

I'll work with what you have given me an see if I can devise a formula to do the formatting.

Just so I have the order correct you have

2002/04/04 08:06:00:00
YYYY MM DD HH MM SS

You want
04/02/02 20:06
MM DD YY HH MM

Good luck
mlmcc
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 

Author Comment

by:DavidNPD
ID: 6980240
I would probably like it to be like this for April 2, 2002 at 8:30 at night.

04/02/02 2030

What I would get right now for that would be
2002/04/02 20:30:00.00

It is already in military time but goes out to seconds and decimals.

Thanks
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 6981469
Try this
Assume your date field is called {dt}
Create a formula in CR  (Converted_Date)
Use Crystal Format

Mid({dt},6,2) & "/" & Mid({dt},9,2) & "/" & Mid({dt},3,2) & " " & Mid({dt},12,2) & Mid({dt},15,2)

To get your date field in select it from the fields list

When built save it and put it in the report next to or below your current date field.  Verify it is what you want and then you can delete the date field from the report or hide it and move the formula to the right spot in the report.

good luck
mlmcc
0
 

Author Comment

by:DavidNPD
ID: 6981491
I replaced the dt field with my own with is in the cfs table called rec_dt. When I attempt to run the formula it states there is an error and that a string is required. This is a dateTime field in the database.




Mid({cfs.rec_dt},6,2) & "/" & Mid({cfs.rec_dt},9,2) & "/" & Mid({cfs.rec_dt},3,2) & " " & Mid({cfs.rec_dt},12,2) & Mid({cfs.rec_dt},15,2)
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 6981548
I looked to see how to convert a date to a string and found a converter from date to datetime

Try this in a different formula (saves typing the other one back in)

CDateTime({cfs.rec_dt})

mlmcc
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 6981549
If that works you may be able to format the field in the report as a datetime field

mlmcc
0
 

Author Comment

by:DavidNPD
ID: 6983685
That function does not appear to be available in a crystal dictionary. I believe the dictionary formula editor is desiegned around Crystal 7 and only has the C7 functions available.
0
 

Author Comment

by:DavidNPD
ID: 6983739
I did find a function that I can use in C7, I used "DTSToDate ({CFS.rec_dt})" to change to a time and a similar to use to go to date. I will then look into combining them back together. When they are placed into the dictionary in this way the format commands can be accessesd.
0
 

Author Comment

by:DavidNPD
ID: 6983755
I spoke a little too soon, The date command will give me access to the formatting but the time still comes over as 08:06:00.00 and you cannot re-format it as a time.
0
 

Author Comment

by:DavidNPD
ID: 6984255
If we can't do this any other way, how about trying to use a trim command to take off the :00.00 from the end of the time? It would have to be in conjunction with the original "DTSToDate ({CFS.rec_dt})" formula
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 6984341
In response to another question DDRYAN3 added the following concerning Crystal dictionaries.
http://www.experts-exchange.com/crystal/Q.20292743.html

Quoting from the online guide for 8.5...

"Dictionaries simply provide all of the convenience with the restrictions." and "... dictionaries are
optional components.  Data can still be access directly by the user."

After re-reading your problem statement, I think you may have misunderstood the point of the Crystal
Dictionaries.  When you use them, you are basically locking the report into whatever data source and
set of fields the dictionary designer has decided you should see.  

I wonder if you rebuild the dictionary after changing the field to datetime if you will have the formatting capabilities you need.

I don't use dictionaries nor have I read much about them.


Just a thought
mlmcc
0
 

Author Comment

by:DavidNPD
ID: 6985876
What I have is a Reporting package that is distributed amongst my users. Most of the more complex reports are already done and hardcoded. What I want to do is to give some of the users the ability to create simple reports in a  slighlty different format from what is given to them already. I am aware of the limitations of the dictionaries, but the advantage of given them a small set of pre-linked tables with Easy to read field names outweighs those limitations IF I can get the time field to look a little more normal for them.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 6987713
Excellent point.

I suspect then you can't get the dictionary rebuilt to include the datetime field as datetime.  I'm not real sure where to go from here.

If I get a chance I'll try to build a dictionary and play with the datetime fields.

mlmcc
0
 

Author Comment

by:DavidNPD
ID: 6988427
Thanks, the problem stems from the fact that the Crystal Dictionaries are based on Crystal v6 (not 7 like I thought) and Crystal 6 did not handle date/time fields well at all.
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 150 total points
ID: 6988991
I tried creating a dictionary in CR8.  The dictionary only supports date formatting and not time formatting.

What does DTSToDate({CFS.rec_dt}) give you?



You might look at

http://support.crystaldecisions.com/communityCS/FilesAndUpdates/cr8_vb_api_activedata.exe.asp

mlmcc
0
 

Author Comment

by:DavidNPD
ID: 6991661
I did in fact try using the DTSToDate({CFS.rec_dt}) command but I still get the formatted time of "08:06:00.00" and it still believes it to be a string and not a time field.

I downloaded that app you referenced and ran it, I do not see the significance, what am I missing?
0
 

Author Comment

by:DavidNPD
ID: 6991747
I'm sorry I just read that wrong, when I use "DTSToDate({CFS.rec_dt})" I get a properly formatted date field that I can reformat with the right click in the report what I am still stuck on is the time portion of the field.
0
 

Author Comment

by:DavidNPD
ID: 6991787
From all the sources I have checked this seems to be a dead issue, it just comes down to the fact that Crystal 6 does not handle DateTime Field well. Does anybody know if you can right trimm 6 digits off of the right side of a text string in Crystal 6? Maybe I can just trim off the characters I dont want.
0
 

Author Comment

by:DavidNPD
ID: 7114032
mlmcc, it looks like this just cannot be done with dictionaries as crystal strands right now, maybe in the next version.
Accept the points for all your help.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Loops Section Overview
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question