Converting CHAR parameter to DATE for comparison with DATE variables

[mikeyh]

Im trying to develop a SQL driven report in Quality Centre using parameters. Ive set up two parameters in the 'query parameters' part of the report which are passed into the SQL, each parameter representing a date and in the format dd/mm/yyyy. I want to be able to check if a variable, of type Date, from one of our tables, falls within that date range given by the two parameters. The variable Im comparing to is of type Date and called BG_USER_30 in the below code, and I believe the parameters are of type CHAR. Ive tried casting the parameters to Date type, but I get an error stating that Date is not a valid data type (even though I have other variables of tpye date in my database). Ive also tried casting all 3 to DateTime, which passes syntax checks but doesnt yield the results im expecting. Below is the section of code which compiles but doesnt select the correct data. Im fairly new to SQL, could anyone offer any pointers as to where Im going wrong here? Is casting totally the wrong way to go? In summary im basically trying to find a way to compare the generic parameters supplied in QC with Date types.

Thanks in advance of any help,

Mike

my code so far is..

...and (cast(BG_USER_30 as Datetime) between cast(@dateone@ as Datetime) and cast(@datetwo@ as Datetime))
Then 1 ELSE 0 END)
As 'Fixed Same Day',

[ck9663]

There are two ways you can handle this.
1. Do it in the front-end.
2. Do it in the back-end, but make sure to check the default of the date format of your SQL Server.

Happy Coding!!!

~~ CK