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

It looks like the query parser or data provider is choking on incorrect data types. Try this: where date between CONVERT(DateTime, '4/1/' + @prmPeriod - 1) and '3/31/' + @prmPeriod + 1) You may also need to use a CAST or CONVERT function on the parameter reference: CONVERT(Int, @prmPeriod) Another option is to convert the entire query to a expression so that it's parsed as a single string. In the Expression Builder, rather than the Query Builder, you would use something like: ="select... from... " & " where date between '4/1/" & Parameters!prmPeriod.Value -1 & "' and '3/31/" & Parameters!prmPeriod.Value + 1 & "'" Paul Turley, MVP, www.SQLServerBIBlog.com *Please vote if helpful*

Well, I have tried this: where date BETWEEN CONVERT(DateTime, '4/1/' + (CONVERT(Int, @prmPeriod) - 1)) AND CONVERT(DateTime, '3/31/' + (CONVERT(Int, @prmPeriod) + 1)) and it's give me this error "String was not recognized as a valid DateTime."

I recommend you use the expression technique I mentioned in my previous post. There are a few examples on my blog, one of them here: https://sqlserverbiblog.wordpress.com/2010/03/11/how-to-parameterize-a-top-values-query/ Paul Turley, MVP, www.SQLServerBIBlog.com *Please vote if helpful*

Hi Devin, Please try the follows, WHERE [date] BETWEEN '4/1/' + CONVERT(VARCHAR(4),YEAR(@prmPeriod)-1) AND '3/31/' + CONVERT(VARCHAR(4),YEAR(@prmPeriod)+1) If any questions, please feel free to ask. Thanks, Eileen

Is it possible to do that via T-SQL? Could be more efficient? DECLARE @y INT SET @y=2010 SELECT * FROM Projects WHERE dt >=cast(rtrim(@y*10000+1*100+1) as datetime) and dt <cast(rtrim((@y+1)*10000+1*100+1) as datetime) Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/