Crystal Report passing a date parameter to a number type field

[PeteyN.O]

I have a query I created in db2 database
select
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
then
case when ppshipm3.date_shipped_sm is null
then ppshipm.date_shipped_sm
else ppshipm3.date_shipped_sm
end
else ppshipm2.date_shipped_sm
end) as ship_dt,
max(price_curr_jbmstr) as sales_amount

from
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)) =
release_jobdet
left join jbsmod.aj_d31t d31 on
substr(ppshipm.job_shipment_sm,1, position(' ', ppshipm.job_shipment_sm)) =
ltrim(rtrim(d31.search_key_uf))
where ppshipm.location_code_sm ='06'
group by
ltrim(rtrim(substr(ppshipm.job_shipment_sm, 1, position(' ', ppshipm.job_shipment_sm))))
order by
ship_dt)
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?

 

[kray4660]

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.

 

[SkipVought]

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.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[hilfy]

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

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

-Dell

Senior Manager, Data & Analytics
Protiviti

http://www.protiviti.com