Sql-server – why to use isnull(field,”) =2

sql server

I have a field to update which uses isnull(field,'') = 2. But I am getting error as the field has character value in it?

for exaample : field = 'E'
It says conversion failed converting varchar value 'E' to datatype int

Best Answer

Your problem is that you are working with two different data types. ISNULL(field,'') implies that you are working with a character field. 2 however is a number. Which do you need?

You can do something like this:

ISNULL(field,'') = '2'

But if field happens to be '02' then the result will be false.

Or if field is actually a number then you could do something like this:

ISNULL(field,0) = 2

But if field is a string data type that you are using to hold numbers and you just happen to have an 'Q' in the field then you are going to get a conversion error.

I'm assuming the first option is best since you are getting the error on 'E'.

When you use the code you actually have it is doing an implicit conversion behind the scenes. If you look at the XML execution plan for the following code:

CREATE TABLE #DataTypeTest (Col1 varchar(50))

INSERT INTO #DataTypeTest VALUES ('1'),('2')

SELECT *
FROM #DataTypeTest
WHERE ISNULL(Col1,'') = 2

You'll see this line (talking about the ISNULL)

 <ScalarOperator ScalarString="CONVERT_IMPLICIT(int,isnull([tempdb].[dbo].[#DataTypeTest].[Col1],''),0)=(2)">

You can see how it's actually converting the ISNULL in to an INT data type. This is because you are comparing it to a integer. It doesn't convert the 2 to a string because INT is higher than VARCHAR on the data type precedence list.

Now when it tries to convert the ISNULL formula into an integer it of course fails when the column contains an E.

Related Solutions

Sql-server – Query to convert the count to million or thousands

This type of formatting is generally best done in your application if possible.

The problem is that the case expression returns a result based on the highest datatype precedence of any branch.

So you would need to cast the final COUNT branch of your CASE to VARCHAR too as int has higher precedence than varchar.

Also you should probably add year into your order by except if you actually want to order Jan 2014 and Jan 2015 together followed by Feb and so on.

Though I might well be minded to take that concatenated string out of the GROUP BY too and rewrite it (Along the way fixing a couple of other issues with integer division and inconsistent boundary conditions) as.

WITH T(mmmyyyy, FORMATTED_COUNT, Yr, Mnth)
     AS (SELECT DATENAME(MONTH, MIN(CREATEDTS)) + ', ' + DATENAME(YEAR, MIN(CREATEDTS)) AS Month,
                CASE
                  WHEN COUNT(*) BETWEEN 1000 AND 999999
                    THEN ( CONVERT (VARCHAR(10), ( CAST(ROUND(COUNT(*) / 1000.0, 1) AS NUMERIC(4, 1)) )) ) + 'k'
                  WHEN COUNT(*) >= 1000000
                    THEN ( CONVERT (VARCHAR(10), ( CAST(ROUND(COUNT(*) / 1000000.0, 2) AS NUMERIC(6, 2)) )) ) + 'm'
                  ELSE CONVERT (VARCHAR(10), COUNT(*))
                END,
                YEAR(CREATEDTS),
                MONTH(CREATEDTS)
         FROM   USAGEDATA
         GROUP  BY YEAR(CREATEDTS),
                   MONTH(CREATEDTS))
SELECT mmmyyyy,
       FORMATTED_COUNT
FROM   T
ORDER  BY Yr,
          Mnth;

Sql-server – Conversion failed when converting the varchar value ‘Hours’ to data type int

This is not clear what output is needed. You are trying to convert text strings to int. This obviously does not work. Besides this type of formatting might fit better outside of SQL Server.

If you want something like 5 Hours 15 Minutes you can try this query:

[Time Spent(In hrs)] =
    CAST(
        SUM(
            DATEDIFF(hour, ld_time_tracker_start_time, ld_time_tracker_end_time)                     
        )
    as varchar(10))
    +' Hours '+ 
    CAST(
        SUM(
            DATEDIFF(minute, ld_time_tracker_start_time, ld_time_tracker_end_time) % 60       
        )
    as varchar(10))
+' Minutes'

Note that I have replaced HH by hour. This is easier to read and understand and it avoid mistakes. See DATEDIFF (Transact-SQL).

Best Answer

Related Solutions

Sql-server – Query to convert the count to million or thousands

Sql-server – Conversion failed when converting the varchar value ‘Hours’ to data type int

Related Question