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.

Students Click Here

Crystal Report passing a date parameter to a number type field

Crystal Report passing a date parameter to a number type field

Crystal Report passing a date parameter to a number type field

I have a query I created in db2 database
ltrim(rtrim(substr(ppshipm.job_shipment_sm, 1, position(' ', ppshipm.job_shipment_sm)))) as job,
max(ltrim(rtrim(paint_code_jobdet))) as paint_code,
max(ltrim(rtrim(d31.alpha_value_uf))) as Shipped_Day,
max(ppshipm.location_code_sm) as plant,
max(ltrim(rtrim(customer_name_jbmstr))) as customer,
max(date_promised_jobdet) as promise_dt,
max(date_requested_jobdet) as requested_dt,
max(case when ppshipm2.date_shipped_sm is null
case when ppshipm3.date_shipped_sm is null
then ppshipm.date_shipped_sm
else ppshipm3.date_shipped_sm
else ppshipm2.date_shipped_sm
end) as ship_dt,
max(price_curr_jbmstr) as sales_amount

jobscopedb.ppshipm as ppshipm
left join jobscopedb.ppshipm ppshipm2 on
ppshipm2.carrier_sm not like '%KIT%' and
substr(ppshipm2.job_shipment_sm,1,position(' ', ppshipm2.job_shipment_sm)) =
substr(ppshipm.job_shipment_sm,1, position(' ', ppshipm.job_shipment_sm))
left join jobscopedb.ppshipm ppshipm3 on
ppshipm3.carrier_sm like '%KIT%' and
substr(ppshipm3.job_shipment_sm,1,position(' ', ppshipm3.job_shipment_sm)) =
substr(ppshipm.job_shipment_sm,1, position(' ', ppshipm.job_shipment_sm))
join jobscopedb.ipjobm on
substr(ppshipm.job_shipment_sm,1, position(' ', ppshipm.job_shipment_sm)) =
job_number_jbmstr and
tax_exempt_1_jbmstr like '%/%' and
percent_jbmstr = 100
join jbsmod.ssacrpdrl1 on
acrjob = job_number_jbmstr
join jobscopedb.ppjobd on
substr(ppshipm.job_shipment_sm,1, position(' ', ppshipm.job_shipment_sm)) =
left join jbsmod.aj_d31t d31 on
substr(ppshipm.job_shipment_sm,1, position(' ', ppshipm.job_shipment_sm)) =
where ppshipm.location_code_sm ='06'
group by
ltrim(rtrim(substr(ppshipm.job_shipment_sm, 1, position(' ', ppshipm.job_shipment_sm))))
order by
as a

where a.ship_dt between 20210201 and 20210228

The ship_dt is a number and formatted in the database as yyyymmdd.

I put this query in a command in Crystal Report and created three parameter that I pass in the query:
StartDate, EndDate, and LocationCode

I put all Parameter as a string and it works as long as you put the data for start and end as yyyymmdd.
I would like for this to be a date parameter where the end user can open the calendar and select a date.

How can I get this to work?

Replies continue below

Recommended for you

RE: Crystal Report passing a date parameter to a number type field

Well you would have the convert the a.ship_dt to a date field (currently it is numeric). I am not sure how you would do that in db2. The the parameters would be date parameters.

RE: Crystal Report passing a date parameter to a number type field

Have the user select a DATE from a Date Picker Calendar.
Convert the DATE to yyyymmdd
Assign that number to you parameter

I'm not a CR so I wouldn't know exactly how to execute that in CR.


glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Crystal Report passing a date parameter to a number type field

To convert the date to a number, try the following:


Year({?DateParam} * 1000 + Month({?DateParam}) * 100 + Day{{?DateParam}) 


Senior Manager, Data & Analytics

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! Already a Member? Login

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