Pass null datetime parameter in powershell

user_7081415 0
Viewed 4921

So I have a powershell script that takes region and datetime as input and internally in the script it calls a stored procedure.

The stored procedure takes two inputs - region and datetimestamp; the datetimestamp is always null. How do I go about parsing it?

function Reset {

param ([string]$region,[nullable[Datetime]]$statustimestamp)

        $conn = New-Object System.Data.SqlClient.SqlConnection("Server=SQL,15010; Database='STG';User ID=SVC;Password=password;Integrated Security=FALSE")
        $conn.Open()
        $cmd = $conn.CreateCommand()
        $cmd.CommandText = "dbo.Reset'$region' ,'$statustimestamp'"
        $adapter = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
        $dataset = New-Object System.Data.DataSet
        [void]$adapter.Fill($dataset)
        $dataset.tables[0]
        $cmd.CommandText    
        $dataset.Tables[0] | Export-CSV M:\MyReport.csv -encoding UTF8 -NoTypeInformation    
        Write-Host 'New report M:\MyReport.csv has been successfully generated' 
    }

I execute it as 

Rest -region IN -statustimestamp NULL

and I get the following error

Reset : Cannot process argument transformation on parameter 'statustimestamp'. Cannot convert value "NULL" to type "System.DateTime".
Error: The string was not recognized as a valid DateTime. There is an unknown word starting at index 0.
At line:1 char:59
+ Reset -region AU -statustimestamp NULL
+ ~~~~
+ CategoryInfo : InvalidData: (:) [Reset], ParameterBindingArgumentTransformationException
+ FullyQualifiedErrorId : ParameterArgumentTransformationError,Reset

powershell
2 Answers

Null in PowerShell is represented by $null and not NULL, that's why the error message is saying the string NULL cannot be converted to a (nullable) DateTime.

Rest -region IN -statustimestamp $null

You can also omit the -statustimestamp parameter altogether.

user_4465150

To complement dee-see's helpful answer:

Note: However your parameters are declared, $PSBoundParameters.ContainsKey('<parameter-name>') inside a script/function tells you whether an argument was explicitly passed to a given parameter (a default value doesn't count); e.g., with the invocation in your question (had it succeeded), $PSBoundParameters.ContainsKey('statustimestamp') would indicate $true.

If you want your parameter value to be $null by omission:

Declare your parameter simply as [Datetime] $statustimestamp and pass no argument to it on invocation; $statustimestamp will then implicitly be $null.

# Declare a parameter in a script block (which works like a function)
# and invoke the script block without an argument for that parameter:
PS> & { param([Datetime] $statustimestamp) $null -eq $statustimestamp }

True  # $statustimestamp was $null

If you want to support explicitly passing $null as an argument:

This may be necessary if you declare a mandatory parameter, yet you want to allow $null as an explicit signal that a default value should be used.

Unfortunately, the specifics of the parameter declaration currently depend on whether the data type of the parameter is a reference type (such as [string] or [System.IO.FileInfo]) or a value type (such as [int] or [datetime]).
You can inspect a given type's .IsValueType property to learn whether it is a value type ($true) or a reference type ($false); e.g.: [datetime].IsValueType yields $true).

If the parameter type is a reference type, you can use the [AllowNull()] attribute:

PS> & { 
  param(
    [AllowNull()]
    [Parameter(Mandatory)]
    [System.IO.FileInfo] $Foo  # System.IO.FileInfo is a *reference type*
  )
  $null -eq $Foo 
} -Foo $null

True  # $Foo was $null

Unfortunately, the same technique doesn't work with value types such as [DateTime], so your parameter must indeed be typed as [Nullable[DateTime], as in your question:

PS> & { 
  param(
    [Parameter(Mandatory)]
    [AllowNull()] # Because the parameter is mandatory, this is *also* needed.
    [Nullable[DateTime]] $Foo  # System.DateTime is a *value type*
  )
  $null -eq $Foo 
} -Foo $null

True  # $Foo was $null

Note: These requirements - needing to pay attention to the difference between value types and reference types and needing to use a [Nullable[T]] type - are obscure and uncharacteristic for PowerShell.

Doing away with these requirements in favor of a unified approach (making it work for value types the way it already does for reference types) is the subject of this proposal on GitHub.

user_453750
Related