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

Common Formulas

Crystal SQL and Date Parameters by HowardHammerman
Posted: 28 Oct 01

If you've run into a problem with a SQL database and Date (or DateTime) selection criteria, you're not alone. It seems that the parameter values want to save with the Crystal SQL, and it just doesn't work properly.

This is a known Crystal anomaly.

A way to work around it is to cast your Date (or DateTime) parameter and database fields as strings (in formula fields), manipulate them so that they're in the format YYYYMMDD, and do your selection on the formula fields. Do this and Crystal ignores them for the purposes of its SQL, which may solve your problem.

For example, say you have a Date-type parameter field called Date0. You can then create a formula called ParamDateStr like this:

StringVar STrDOB := ToText({?Date0});
NumberVar NumSlash1 := InSTr(StrDOB, "/");
NumberVar NumSlash2 := InStr(NumSlash1+ 1, STrDOB, "/");
StringVar StrYr := mid(StrDOB, NumSlash2 + 1, 4);
StringVar StrMo := left(STrDOB, NumSlash1 - 1);
StringVar StrDay := mid(STrDOB, NumSlash1 + 1, NumSlash2 - NumSlash1 - 1);
NumberVar NumMoLen := length(StrMo);
if NumMoLen < 2 then
StringVar StrMo := "0" + StrMo;
StringVar StrDay := mid(STrDOB, NumSlash1 + 1, NumSlash2 - NumSlash1 - 1);
if length(StrDay) = 1 then
StringVar StrDay := "0" + StrDay;
StrYr + StrMo + StrDay

What you've done is to parse the Date, then rearrange the year, month and day values (making sure that months and days are two characters in length). Now, for your database field:

StringVar STrDOB := ToText({Table.DateField});
NumberVar NumSlash1 := InSTr(StrDOB, "/");
NumberVar NumSlash2 := InStr(NumSlash1+ 1, STrDOB, "/");
StringVar StrYr := mid(StrDOB, NumSlash2 + 1, 4);
StringVar StrMo := left(STrDOB, NumSlash1 - 1);
NumberVar NumMoLen := length(StrMo);
if NumMoLen < 2 then
StringVar StrMo := "0" + StrMo;
StringVar StrDay := mid(STrDOB, NumSlash1 + 1, NumSlash2 - NumSlash1 - 1);
if length(StrDay) = 1 then
StringVar StrDay := "0" + StrDay;
StrYr + StrMo + StrDay

What you have NOW is two strings, each created from a date, and each formatted identically as YYYYMMDD. Finally, your selection criterion uses these two formula fields:

{@DOBString} >= {@ParamDateStr}

or whatever makes sense under your circumstances.


Howard Hammerman, Ph.D.
Hammerman Associates, Inc.
http://www.hammerman.com
800-783-2269
Hammerman Associates, Inc. provide Crystal Reports training,
consulting, course material, utilities and software. Consultants are available throughout North America for short or long-term assignments.

Back to Business Objects: Crystal Reports 4 Other topics FAQ Index
Back to Business Objects: Crystal Reports 4 Other topics Forum

My Archive

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