Powershell function to check input and return datatype is not returning correct values

Question

I'm using a powershell script to assist with database updates/parsing/etc. Because our database servers do not allow us to do queries and updates within the same script, I need to actually query data, parse it on my computer with powershell, and then build a new update statement from the parsed query. In order to assist with this, I need to correctly cast datatypes between powershell and SQL. I wrote the following function to assist with this, but it is not correctly parsing timestamps, and any timestamp passed to it is causing the function to return 'string' and leave the $setDate variable as $null

$myDate = '5/10/2023'

function getDataType {
   param($inputData)
   
   #check if input data is null
   if (!$inputData) {
      return 'null'
   }
   
   #check if variable can be cast as a timestamp
   try {
      #build an array of possible timestamp formats
      $dateFormats = @('M/d/yyyy','M/dd/yyyy','MM/d/yyyy','MM/dd/yyyy','MM-dd-yyyy HH:mm:ss','MM-dd-yyyy HH:mm','yyyy-MM-dd HH.mm.ss')
      
      foreach ($format in $dateFormats) {
         if ([DateTime]::TryParseExact($inputData, $format, $null)) {
            $global:setDate = [datetime]::ParseExact($inputData, $format, $null).ToString('yyyy-MM-dd HH:mm:ss')
            return 'timestamp'
            break
         }
      }
   }
   catch {
      write-verbose 'not a timestamp'
   }
   
   #check if data is a number
   try {
      if ([int]$inputData) {
         return 'number'
      }
   }
   catch {
      write-verbose 'not a number'
   }
   
   #assumes input is a string if it is not null, not a date, and not a number
   return 'string'
}

getDataType($myDate) #should return 'timestamp' but returns 'string'

write-host "the date is $setDate" #should return "the date is 2023-05-10 00:00:00" but returns nothing

Answer 1

The main issue with your code is that you're missing arguments for your [DateTime]::TryParseExact call and its throwing an error, you're just not seeing it because Write-Verbose doesn't have a -Verbose argument to produce verbose output, a minimal example:

try {
    [DateTime]::TryParseExact('5/10/2023', 'M/dd/yyyy', $null)
}
catch {
    Write-Verbose 'not a timestamp' -Verbose
}

# Outputs: VERBOSE: not a timestamp

The correct method call would be to add the missing arguments to this overload:

[DateTime]::TryParseExact(
    '5/10/2023',                                 # string s
    'M/dd/yyyy',                                 # string? format
    [cultureinfo]::InvariantCulture,             # IFormatProvider? provider
    [System.Globalization.DateTimeStyles]::None, # DateTimeStyles style
    [ref] [datetime] 0)                          # out DateTime result

It also seems you're looking to make repeated calls to this function, you should note that repeated function calls is expensive, for that I would recommend you to change your function for a static method which don't suffer from this. Here is a working version of what you were looking to accomplish:

class MyType {
    static [string[]] $Formats = @(
        'M/d/yyyy'
        'M/dd/yyyy'
        'MM/d/yyyy'
        'MM/dd/yyyy'
        'MM-dd-yyyy HH:mm:ss'
        'MM-dd-yyyy HH:mm'
        'yyyy-MM-dd HH.mm.ss'
    )

    static [string] GetDataType([object] $inputData, [ref] $out) {
        if([string]::IsNullOrWhiteSpace($inputData)) {
            return 'null or empty string'
        }

        if([int]::TryParse($inputData, [ref] $null)) {
            return 'its an integer'
        }

        # no need for a loop here, `TryParseExact` has an `string?[]? formats` overload for this ;)
        $isDate = [datetime]::TryParseExact(
            $inputData,
            [MyType]::Formats,
            [cultureinfo]::InvariantCulture,
            [System.Globalization.DateTimeStyles]::None, # You should define this one
            $out)

        if($isDate) {
            return 'its a datetime'
        }

        return 'undetermined string'
    }
}

$myDate = '5/10/2023'
$myOutVar = [datetime]::new(0)
$result = [MyType]::GetDataType($myDate, [ref] $myOutVar)

if($result -eq 'its a datetime') {
    return $myOutVar
}

$result