Formatting dates in Crystal Dictionaries

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.
DavidNPDAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DavidNPDAuthor Commented:
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
mlmccCommented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

DavidNPDAuthor Commented:
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
mlmccCommented:
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
DavidNPDAuthor Commented:
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
mlmccCommented:
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
mlmccCommented:
If that works you may be able to format the field in the report as a datetime field

mlmcc
0
DavidNPDAuthor Commented:
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
DavidNPDAuthor Commented:
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
DavidNPDAuthor Commented:
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
DavidNPDAuthor Commented:
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
mlmccCommented:
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
DavidNPDAuthor Commented:
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
mlmccCommented:
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
DavidNPDAuthor Commented:
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
mlmccCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DavidNPDAuthor Commented:
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
DavidNPDAuthor Commented:
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
DavidNPDAuthor Commented:
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
DavidNPDAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.