We help IT Professionals succeed at work.

Crystal Reports truncating date based on mask specified in input

kbcoder
kbcoder asked
on
140 Views
Last Modified: 2018-04-26
Hello!

I am using crystal 2008 with Oracle 12c.

I built a simple report with command statement as follows.

select to_char(sysdate, '{?date_mask}')  as date_formatted from dual

The problem I run into is reproducible by following the steps in the same order;

1. Put in the code in the command statement and hit ok.
2. Enter the mask "dd-mm-yy" once the param input pops up.
3. Hit OK. Report shows the current date as - 29-03-17
4. Now, refresh the report and enter a new mask - dd-mon-yyyy and hit OK
5. Report shows the date cut off to the length of the previous mask entered (i.e., to 8 characters) - 29-mar-2

Solutions I have tried:
1. Putting in a longer format as a default value to force crystal to consider the max length. - Did not work.
2. RPAD the date formatted code to some 15 or more characters. - this works but not a preferred solution.

If any one of you have faced something like this, please share the solution(s) that worked for you.

Thank you!
KB.
Comment
Watch Question

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
What happens if you reverse the order of the mask entries?
Try using the longer format the first time you run the report.

mlmcc

Author

Commented:
When I try a longer format the first time I use, and shorter the next time, it works fine. It is only the other way round. I just ran it in Crystal 2013 and it has the same problem there as well.
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Have you set the width of the field so it is wide enough to handle the longest date format?

mlmcc
CERTIFIED EXPERT

Commented:
As I read it, your datasource is actually changing.  The dd-mm-yy format creates an 8 character string field in the output from your Command.  My guess is that when you use dd-mon-yyyy, the field changes to 11 characters, but CR is still using the 8 character size, so the last 3 characters are cut off.  You could check that by running the report using dd-mm-yy and then dd-mon-yyyy, and then check the field description in the Field Explorer (in older versions of CR, you could right-click on the Field Explorer and there was an option to have it show the field types and lengths).  I'm guessing that CR will still show the field as 8 characters after you use the dd-mon-yyyy format.

 If I'm right, doing a "verify database" after running the report with the dd-mon-yyyy format would probably get CR to see the new field size, and the date wouldn't be cut off anymore, but that's not much of a solution.  The real solution would be to set a fixed size for that column in your Command, so that the datasource isn't changing anymore.  I'm guessing that that's basically what you were doing with RPAD.  In MS SQL, I would make it a varchar column with however many characters you need for the longest format  (varchar columns only use the required number of characters, so allowing for more characters doesn't waste space), but I don't know if Oracle has anything like that.

 James

Author

Commented:
@mlmcc - Yes, there is "can grow" set on the field.

@James0628 - You have relayed it all correctly. What I would like to know is that I see the report is always the environment I connected to. How would the datasource change or what does it mean when the datasource is changing?

I did verify datasource and the string value is shown correctly and I am not sure how to set the field characters to a certain length since this is a formatted field inside sql. One other thing I tried doing was to set a default value to the date mask param with the longest date format so it assumes it as the default but then once I give a shorter mask to start with, it ignores the length of the default date mask value.

Thanks to both of you for writing in.
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
James, CAST function does work. Thanks for suggesting that one. I think this is the best I can do in the given situation.

On the other hand, the surprising thing is that I have this code working (without cast) in another environment. CR and Oracle versions are the same. It is getting hard to understand what is causing it to work in one and not the other env.

Author

Commented:
I'll go ahead mark your solution as the best solution once we close the loop on this.
CERTIFIED EXPERT

Commented:
What do you mean by "another environment"?

 FWIW, I'm not sure where the initial size for that field comes from.  It may just be some default size, but I think that it may come from the very first time that you run the report after creating the Command.  If so, then if you happened to use the longer format that first time, then the initial field length would be longer and you wouldn't have that problem (unless you tried using a format that was even longer than that first one).

 James

Author

Commented:
Another Environment = another database.

Yes, There's something on those lines that is happening. I'll have to dig further. I guess for now this workaround resolves the current problem. Thanks for the help!
CERTIFIED EXPERT

Commented:
You're welcome.  Glad I could help.

 James

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.