It’s been a long time (over 2 months) since I last posted.  I’ll try to get back into a rhythm of posting at least weekly.  Anyway, this is something that occurred to me at work when writing a script.

I usually avoid nullable columns, but sometimes date fields make sense to be null (rather than use sentinel values like 1/1/1900).  In this case, I had a nullable date column and I needed to check in PowerShell whether the field was in fact null or not.  In SQL, I would have just used an IS NULL, or used the IsNull() function to replace the null value with something a little easier to deal with.  My first (feeble) attempt was to do this:

if (!$_.completedDate){
# it’s null
}

Unfortunately for me, that doesn’t work.  Next, I used this (which worked, but wasn’t very satisfactory either):

if ($_.completedDate.ToString() -eq ''){
# it’s null
}

Realizing that I was being stupid, I googled “PowerShell SQL NULL and after looking at several pages which didn’t really address the issue, I found this. A little work to change it into a function, and voilà.

function is-null($value){
  return  [System.DBNull]::Value.Equals($value)
}

A few quick tests and this is what I wanted. Now, my code looks like this:

if (is-null $_.completedDate){
# it’s null
}

I find it hard to believe I haven’t written this function before (or seen it).

By the way…be watching the SQL PowerShell Extensions project.  Chad released version 2.1, which includes SQL mode for the ISE (really nice).  I know he and several others are collaborating on an update which should be out sometime soon.

-Mike