Conversion failed when converting date and/or time from character string in SQL Server - why?

Question

SET @tableHTML = 
@tableHTML +N'<tr><td colspan="2"> 1st email call T -3 = ' + DATEADD(day,-3, @Leaguedate) + '</td></tr>'

I'm getting this error message:

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

Possible duplicate of [Conversion failed when converting date and/or time from character string while inserting datetime](https://stackoverflow.com/questions/14119133/conversion-failed-when-converting-date-and-or-time-from-character-string-while-i) — m00am, Mar 09 '18 at 10:04
According to the rules for [data type precedence](https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql), when you combine a `DateTime` (Assumed type of `@LeagueDate`.) and a string in an expression, SQL Server will convert the string to a `DateTime` first. If you want to convert the `DateTime` to a string you must do it explicitly, e.g. with `Cast` or `Convert`. — HABO, Mar 09 '18 at 14:16

score 0 · Accepted Answer · answered Mar 09 '18 at 12:07

You must convert the datetime value to string (char/varchar/nchar/nvarchar).

The following statement:

SELECT 'string ' +  DATEADD(day,-3, GETDATE()) +' another string'

Will result with:

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

However this statement:

SELECT 'string '+  CONVERT(char(10), DATEADD(day,-3, GETDATE()), 103) +' another string'

Will result with:

string 06/03/2018 another string

1 Answers1