I have a date field where I created a parameter @YearApplication and I need to use that parameter to query the dataset. How can pass a year only?? The user does not want to enter the start date and end date. AngieAngie Rivera

Hi, You can use following query, if your @yearapplication is datetime.. select * from <<table>> where <<datefiled>> between DATEADD(yy, DATEDIFF(yy,0,@YearApplication), 0) and DATEADD(yy, DATEDIFF(yy,0,@YearApplication)+1, 0) or if it's int value like 2000,2002, 2003..You can use below query select * from Production.TransactionHistory p where p.ModifiedDate between DATEADD(yy, @YearApplication - 1900, 0) and DATEADD(yy, @YearApplication - 1899, 0) - Chintak (My Blog)

Chintak: I tried the other query: BETWEEN DATEADD(yy, DATEDIFF(yy, 0, @YearApplication), 0) AND DATEADD(yy, DATEDIFF(yy, 0, @YearApplication) + 1, 0) and I am getting the same error message "Failed to convert parameter value from a Decimal to a DateTime" Angie Rivera

In your sql try Where year(dbo.tblZEDActn.DateAssigned) = @YearApplication. You may need to change the param type to int. So when the user types 2011, the report only returns 2011 dates. ssrs user-Report Writer 'SSRS User'

This one Works!!! Thanks a lotAngie Rivera

Chintak: I am getting this error: "Failed to convert parameter value from a Decimal to a datetime" Angie Angie Rivera

Chitak: My parameter is a datetime and this is the Where condition that I put in the dataset: WHERE (dbo.tblZEDActn.DateAssigned BETWEEN DATEADD(yy, @YearApplication - 1900, 0) AND DATEADD(yy, @YearApplication - 1899, 0)) Message read "FAiled to cnvert parameter value from a Decimal to a Datetime. AngieAngie Rivera

Hi, If it's datetime, then use following query select * from <<table>> where <<datefiled>> between DATEADD(yy, DATEDIFF(yy,0,@YearApplication), 0) and DATEADD(yy, DATEDIFF(yy,0,@YearApplication)+1, 0)- Chintak (My Blog)

Hi, You should not do this if you have index on the dbo.tblZEDActn.DateAssigned column. If you use year(dbo.tblZEDActn.DateAssigned) then that index will not be used and because of that your query will perform lot slower and effectivly your report will become slower. That's why i was suggesting other query.- Chintak (My Blog)

Hi, I have a similar problem when I was trying to concatenated the date because all I want from the user is to input the year. The data type for the parameter is text, but it doesn't matter what data type I changed to it still gives me an error "Failed to convert parameter value from a Decimal to a DateTime". Here is the syntax in SSRS 2008 that I have tried. select (fields) from (Table) where date BETWEEN '4/1/' + DATEADD(yyyy, DATEDIFF(yyyy, 0, @prmPeriod - 1), 0) AND '3/31/' + DATEADD(yyyy, DATEDIFF(yyyy, 0, @prmPeriod + 1), 0) or where date between '4/1/' + STR(@prmPeriod-1,4,0) and '3/31/' + STR(@prmPeriod +1,4,0) Thanks