Gowtham Ramamoorthy Gowtham Ramamoorthy - 6 months ago 6
SQL Question

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

I'm trying to get a select statement which gives me the values between particular date values. But I wasn't able to complete this one because of the above mentioned error.

I have created a sample code below which replicates the error.

Is there any solution to resolve this one ?

BEGIN
DECLARE @OutputTimeDimensionvalue AS VARCHAR (60)
SET @OutputTimeDimensionvalue = '2006-onward'

CREATE TABLE #TEMP1
(
Timevalue int,
TimedimensionDate date
)

INSERT INTO #TEMP1 VALUES (23,'2007-04-01'),(28,'2008-01-01'),(45,'2012-04-01')

select Timevalue from #TEMP1 where TimedimensionDate BETWEEN CONVERT(datetime,LEFT(@OutputTimeDimensionvalue, 4)+'-01-01') AND
CONVERT(datetime,RIGHT(@OutputTimeDimensionvalue, 4)+'-01-01')

DROP TABLE #TEMP1
END

Answer

You must use LEFT instead of RIGHT to get the year part:

SELECT Timevalue
FROM #TEMP1
WHERE
    TimedimensionDate BETWEEN 
        CONVERT(DATETIME, LEFT(@OutputTimeDimensionvalue, 4) + '-01-01') 
        AND CONVERT(DATETIME, LEFT(@OutputTimeDimensionvalue, 4) + '-01-01')

However, I would suggest using DATE type instead and then using >= AND < for comparison:

DECLARE @OutputTimeDimensionvalue AS DATE = '20060101'

SELECT Timevalue
FROM #TEMP1
WHERE
    TimedimensionDate >= @OutputTimeDimensionvalue 
    TimedimensionDate < DATEADD(YEAR, 1, @OutputTimeDimensionvalue)

The above will give you TimeValues in 2016.