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
