Convert date parameter to number in Crystal command

[BandOQ4b]

I am using crystal command to write my own SQL. I need to add a parameter (in the command window, not in the report) that is the users see as a date but I need to convert that to a number format YYYYMMDD to match the records in the database (they will not change the database). They insist on date parameter.

I am using Crystal 2008 connecting to a AS400 via a IBM iSeries ODBC driver DB2 UDB. (Straight SQL does not work, not all DB2 SQL works either, it is some kind of weird Crystal/SQL/DB2 driver hybred) Assistance GREATLY appreciated!!!!

Sample code:
Sales.DATE >= {?Begin Date} and
Sales.DATE <= {?End Date}

Sales.date is number field
parameter field is date field

I have tried converting the database field to date (it works) but it is too slow. As a test I set the parameter to number field and it is very quick!

 

[GJParker]

Convert the param to a string and then substring out the values you need leaving you with the correct number as string and then convert this string to a number.

Its hard to be more precise as I don't know the functions available to you on AS400 nor the format of the date you are inputting.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[BandOQ4b]

I used your idea. I built a test report, Crystal command does not give any errors and accepts the code.

FYI- I can't use "CONVERT" instead of "CAST" this system doesn't like it

Sales.DATE >= CAST (CHAR ({?Begin Date}) as DEC(8, 0))
and Sales.DATE <= CAST (CHAR ({?End Date}) as DEC(8, 0))
But when I try to run the report i get the following error.

Database Connector Error: 'HY000:[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL802 - Data conversion or data mapping error. [Database vendor Code: -802]'

Suggestions???

 

[lbass]

Not sure what functions you can use, but try using the equivalent of the following:

Sales.DATE >= to_number(to_char({?Start Date},'YYYYMMDD'))
and
Sales.DATE <= to_number(to_char({?End Date},'YYYYMMDD'))

-LB

 

[BandOQ4b]

The closest that I could come up with that "command" would accept was

sales.DATE >= DEC ((SUBSTR (CHAR ({?Begin Date}), 5, 4)) ||
(SUBSTR (CHAR ({?Begin Date}), 1, 2)) ||
(SUBSTR (CHAR ({?Begin Date}), 3, 2)))
and sales.DATE <= DEC ((SUBSTR (CHAR ({?End Date}), 5, 4)) ||
(SUBSTR (CHAR ({?End Date}), 1, 2)) ||
(SUBSTR (CHAR ({?End Date}), 3, 2)))

However when run I got the same error as above.

I will keep trying any suggestions put forth.

 

[BandOQ4b]

I set the parameter field up in SELECT, so I could see the output.
CHAR ({?End Date}) as TEST_END
puts the date in the correct format of YYYYMMDD.
When I try to use DECIMAL to convert the string to a number nothing is returned in the field (TEST_END) in the report.

DECIMAL (CHAR ({?End Date}), 8, 0) as TEST_END

It seems like it should work, but doesn't.... Suggestions?

 

[BandOQ4b]

Finally found the answer, thanks everyone for your help!

When converting a date it must first be converted to a charater string, when converted it also flips the data in the correct order (YYYYMMDD) matching the database. However, in my database it also adds dashes between field parts (i.e. 2010-07-10). So,when converting from CHAR to a DECIMAL, these dashes must be eliminated, otherwise the code errors out.
Here is the code

Sales.DATE >= CAST
(
(SUBSTR (CHAR ({?BEGIN Date}), 1, 4) ||
SUBSTR (CHAR ({?BEGIN Date}), 6, 2) ||
SUBSTR (CHAR ({?BEGIN Date}), 9, 2)
) as DEC(8, 0)
)

Keep this in your bag of tricks, it may come in handy! It took me a long time to figure this out!!!!!!

 

[timolria]

writing command in sql to set condition to prevent reading of records when params 1, 2, 3 are null. Section in rpt is set conditionally to return a message to user that one param must be used but I'd like to prevent command from reading records also - would this be a case statement or ?