Thread: Command Object - Parameters

I'll do the best I can to explain my dilemma. I'm using Crystal XI and have a report where I use several Command Objects and link them in the report.

Each command object is a SQL statement for a day of the week.

EX: Command1 = Sun
Command2 = Mon
Etc

Within the SQL statement, I set up a parameter on the "Where" clause of each Command object that looks like this:

Work.Date={?Sun}
Work.Date={?Mon} etc

The Work.Date field is an int datatype, not an actual date field. In most of my raw SQL statements, I do a conversion such as this:

Cast(
Substring(Convert(Varchar,Work.Date),5,2)+'-'+
Substring(Convert(Varchar,Work.Date),7,2)+'-'+
Substring(Convert(Varchar,Work.Date),1,4) as Datetime)

This will typically get the format that I need. When I do this in the Select statement of the Command Object, it returns the data in the Crystal Report in the correct format.

However, what I'd like to do is something like this:

WHERE
Cast(
Substring(Convert(Varchar,Work.Date),5,2)+'-'+
Substring(Convert(Varchar,Work.Date),7,2)+'-'+
Substring(Convert(Varchar,Work.Date),1,4) as Datetime)={?Sun}

and set the parameter to datatype Date or Datetime in the Create Parameters box so I can use the calendar to select dates on prompt. However, it's throwing an error each time I run that. Does anyone have any suggestions?

Thanks!

Hi J,

Here's a sample command object query that I used:
SELECT *

FROM WORK

WHERE
Cast(
Substring(Convert(Varchar,Date),5,2)+'-'+
Substring(Convert(Varchar,Date),7,2)+'-'+
Substring(Convert(Varchar,Date),1,4) as Datetime)={?Test}

(Set datatype in parameter list to both Date and Datetime)

Here's the error:

Failed to retrieve data from the database.
Details: 22007:[Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting datetime from character string. [Database Vendor Code: 241]

Not sure if that helps

Hi J,

Thanks for the reply.

the Work.Date field is data type INT. It's an integer
stored in an 8 digit number in yyyymmdd format but it
is only date field in name, not in type.

The conversion is necessary in order to convert it from data
type INT to data type Datetime. My supposition for the parameter
list was that I'd need to get it converted to a datetime data type
in order to use the calendar.

I hope that makes sense!

Great ideas guys! I hadn't even thought of that. Yes, there will always be dates=0 because the field is not nullable. And there are mistakes in old data that I'm not allowed to change so we'll have some values from before the 20th century.

Sooo...my work around was adding the following filters in the where clause:

And Work.Date>0
AND LEN(Work.Date)=8

Thank you so much, that helps immensely!