CODEWITHSUNDEEP
sqlsql-server-2008t-sql
KentZhou
KentZhou

Reputation: 25553

Isnull and data type in SQL Server

Suppose I have a table like: 

Tab(MyDate datetime null, MyIs bit null, ...)

Then I have SQL Like 

    select Isnull(MyDate, 1), IsNull(MyIs, 999) from Tab;

If MyDate, MyIs value is null in DB, the result is:

    MyDate                   MyIs  
    1900-01-02 00:00:00.000  1

Not the expected value like 

MyDate        MyIs  
1             999

So it looks like the return value is based on data type of the column. How to resolve this issue?

For my testing case as above, I Only got one row from the table.

Upvotes: 2

Views: 5888

Answers (4)

Diego
Diego

Reputation: 36146

you can do 

select 
    case when mydate is null
    then '1' else cast(mydate as varchar(40))
    end,
    case when MyIs is null
    then 999 else MyIs
    end
from tab

enter image description here

Upvotes: 0

Kashif
Kashif

Reputation: 14430

SQL servers "zero" date or base date is 1900-01-01 so you are just converting MYDate null with 1 so it is adding 1 day in base date as 

declare @d datetime = 1
select @d

1900-01-02 00:00:00.000

set @d = 0
select @d

1900-01-01 00:00:00.000

MyIs is a bit type so anything except 0 is '1' like 

declare @b bit = 0
select @b

set @b = 1
select @b

set @b = -1
select @b

Above behavior of columns because of different data types

Upvotes: 0

MatBailie
MatBailie

Reputation: 86706

The way you're looking for, you don't.

All the values in the same column must be the same data type. If your data had NOT NULL values in some rows (returning the column's data-type) and NULL values in other rows (returning a different data type, as per your CASE statement), you would break this requirement.

You could have two extra fields...

select
  MyDate,
  CASE WHEN MyDate IS NULL THEN 1   ELSE 0 END As MyDateWibble,
  MyIs,
  CASE WHEN MyIs   IS NULL THEN 999 ELSE 0 END AS MyIsWibble
FROM
  Tab;

You could CAST() everything to VARCHAR() and return strings instead of DATETIME, BIT or INT.

But, more probably, I'd review the reason you want to do this in the first place and work out an alternative design.

Upvotes: 4

buckley
buckley

Reputation: 14079

You are mixing up the datatypes.

For example, when you test for the data you return integer 1. This will get translated to the data where sql server starts to count from + 1 (day)

Upvotes: 0

Related Questions