CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Infant Dev
Infant Dev

Reputation: 1749

Check multiple columns for null in SQL Server 2008

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.

Upvotes: 4

Views: 10192

Answers (3)

Sergio
Sergio

Reputation: 6948

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

Upvotes: 8

Terning
Terning

Reputation: 144

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();
}

Upvotes: -2

Kaf
Kaf

Reputation: 33809

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

Upvotes: 3

Related Questions