sql - ERROR:Conversion failed when converting date and/or time from character string.(when date data type is used in filter) -

i'm trying select statement gives me values between particular date values. wasn't able complete 1 because of above mentioned error.

i have created sample code below replicates error.

is there solution resolve 1 ?

begin declare @outputtimedimensionvalue varchar (60) set @outputtimedimensionvalue = '2006-onward'  create table #temp1 ( timevalue int, timedimensiondate date )  insert #temp1 values (23,'2007-04-01'),(28,'2008-01-01'),(45,'2012-04-01')  select timevalue #temp1 timedimensiondate between convert(datetime,left(@outputtimedimensionvalue, 4)+'-01-01')  ,  convert(datetime,right(@outputtimedimensionvalue, 4)+'-01-01')  drop table #temp1 end 

you must use left instead of right year part:

select timevalue #temp1     timedimensiondate between          convert(datetime, left(@outputtimedimensionvalue, 4) + '-01-01')          , convert(datetime, left(@outputtimedimensionvalue, 4) + '-01-01') 

however, suggest using date type instead , using >= , < comparison:

declare @outputtimedimensionvalue date = '20060101'  select timevalue #temp1     timedimensiondate >= @outputtimedimensionvalue      timedimensiondate < dateadd(year, 1, @outputtimedimensionvalue) 

the above give timevalues in 2016.