CODEWITHSUNDEEP
sqlsql-server
nina_dev
nina_dev

Reputation: 695

Conversion failed when converting the varchar value 'something' to data type int

I have the following code:

,iif(([CreatedDateTime] IS NOT NULL 
AND 
migrationdatetime IS NOT NULL), datediff(day,[CreatedDateTime],[MigrationDateTime]),'not moved to prd')

and basically if either of these date is null I want to display a text. is there any reason why I get this error"Conversion failed when converting the varchar value 'something' to data type int" How can I resolve it? Appreciate your help

Upvotes: 0

Views: 2191

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269523

An expression is an expression is an expression. And -- like the colors of many roses -- has only one type. It cannot be both a string and a date.

By the type precedence rules of SQL, when a string and a number are the choices, the number is the dominant type; so the string is converted to a number. And you get a conversion error.

So, use CONVERT(), CAST() or FORMAT():

(CASE WHEN CreatedDateTime IS NOT NULL AND migrationdatetime IS NOT NULL
      THEN CONVERT(VARCHAR(255), DATEDIFF(DAY, CreatedDateTime, MigrationDateTime))
      ELSE 'not moved to prd'
 END)

Of course, given the rules of NULLs in SQL, this is more simply written as:

      COALESCE(CONVERT(VARCHAR(255), DATEDIFF(DAY, CreatedDateTime, MigrationDateTime)),
               'not moved to prd'
              )

Upvotes: 3

Arpit Srivastava
Arpit Srivastava

Reputation: 9

I used the same but it works perfect

DECLARE @CreatedDateTime Varchar(30) = getdate(), @migrationdatetime  Varchar(30) = dateAdd(MM,2,getdate())

Select iif((@CreatedDateTime IS NOT NULL AND @migrationdatetime IS NOT NULL), datediff(day,@CreatedDateTime,@MigrationDateTime),'not moved to prd')

I think date format in varchar is not correct. you need to look into the record

Upvotes: 0

Related Questions