Conversion failed when converting date and/or time from character string -

Question

I have below query:

declare @migdate as varchar(10)
set @migdate = 20140501
SELECT ID, (CASE WHEN a.HD ='abc' 
   THEN DATEADD(YEAR,1,a.LTST_DATE)
   ELSE 
       CASE WHEN DATEDIFF(MONTH,a.LTST_DATE,@MIGDATE) > 0
           THEN DATEADD(MONTH,DATEDIFF(MONTH,a.LTST_DATE,@MIGDATE)+1,a.LTST_DATE)
           ELSE '99991231'
       END
   END) AS NEXT_CHARGE_DATE
from table a

Note: LTST_date in table is varchar as well.

I am getting error

Conversion failed when converting date and/or time from character string.

Am I doing anything wrong here?

Answer 1

@migdate is a varchar, so how can you do a datediff or dateadd on it, they are date operations not string ones? You need to convert or cast your variable to the datetime or date or datetime2 data types. i.e.:

declare @var varchar(10) = '20050305'
select datediff(YYYY, getdate(), convert(datetime, @var, 100))

Note the 100 is the format of the datetime string, look in Books Online for the various formats.

Also in one case your returning a datetime type (from the DateAdd), but in the other branch you're returning a varchar. What data type do you need in that column?

Answer 2

The third parameter of the function DATEADD and the second parameter of the function DATEDIFF they should be dates. But in your case they are strings (you said that LTST_date in table is varchar). So either change your table so LTST_date is a date or convert the LTST_date from string to date before you apply LTST_date in the functions

Answer 3

DECLARE @migdate AS DATE = '20140501'

SELECT  ID, 
        (
        CASE    WHEN a.HD ='abc'    THEN DATEADD(YEAR,1,a.LTST_DATE) 
                ELSE    CASE    WHEN DATEDIFF(MONTH,a.LTST_DATE,@MIGDATE) > 0 THEN DATEADD(MONTH,DATEDIFF(MONTH,a.LTST_DATE,@MIGDATE)+1,a.LTST_DATE) 
                ELSE '99991231' END END
        ) AS NEXT_CHARGE_DATE
FROM    [table] a