Check multiple columns for null in SQL Server 2008

Question

Table A:

Id  Name  DateCreated  DateModified
-----------------------------------
1    A     2013-1-12    2013-1-15
2    B     NULL         2013-2-1
3    C     NULL          NULL

I have to migrate this table's data to another table in which DateCreated is a not nullable column, and the conditions are if DateCreated is null use DateModified and if both are null use current date.

I can't use

ISNull(DateCreated,DateModified)

because both can be null.

How do I do it? The table has around 10000 rows.

Sergio · Accepted Answer · 2017-07-18 09:03:51Z

8

You can use COALESCE(DateCreated, DateModified, GETDATE())

edited Jul 18, 2017 at 9:03

answered Feb 25, 2013 at 13:00

Sergio

6,9485 gold badges33 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Indi · Accepted Answer · 2013-02-25 13:05:06Z

3

Nested Isnull

Isnull(DateCreated,isnull(DateModified, getdate()))

Or use a CASE

case when DateCreated is null and DateModified is null then getdate()
     when DateCreated is null then DateModified 
     else DateCreated end

answered Feb 25, 2013 at 13:05

Indi

33.9k7 gold badges61 silver badges80 bronze badges

2 Comments

Sergio Over a year ago

coalesce does all this stuff: msdn.microsoft.com/en-us/library/ms190349.aspx

Infant Dev Over a year ago

colesce has some precedence problem, thats what I knew, but in this case since the data types are same both will work. Marking this as correct answer, because it was answered first. Both are actually correct, and so an upvote for both.

Terning · Accepted Answer · 2013-02-25 13:19:47Z

-2

is is simething like this you are looking for?

$result = mysql_query($query, $link);

if ($result) {
  while($row = mysql_fetch_array($result)) {

    if ($row['DateCreated'] == 'NULL'){

        if ($row['DateModified'] == 'NULL'){

                $today = date("Y-m-d");
                $id = $row['id'];
                $query = sprintf("SET DateCreated = $today WHERE id = $id");

            } else {
                $newDate = $row['DateModified'];
                $id = $row['id'];
                $query = sprintf("SET DateCreated = $newDate WHERE id = $id");}
        }
  }

}
else {
  echo mysql_error();
}

answered Feb 25, 2013 at 13:19

Terning

1442 silver badges10 bronze badges

Collectives™ on Stack Overflow

Check multiple columns for null in SQL Server 2008

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related