How can I ingest random-formatted dates in PowerShell

Question

I have a login tracker log file that is an amalgamation of multiple sources. The sources (and there are many) use a variety of date formats. I am using the resultant $objList object to hand these over to a SQL Database. When I try to then use SQL Queries, I am missing data.

Here is a small slice of the raw input, from September 2007:

1. Logon;Username;Server01;10/09/2007 09:56:40
2. Logon;Username;Server02;10/09/2007 11:26:20
3. Logon;Username;Server03;9/11/2007 10:16:27 AM
4. Logon;Username;Server04;11/09/2007 12:28:45

Notice the 3rd one is American format, the others are European. I need a way of getting these things to ingest into a script in a consistent date format. There are literally hundreds of thousands of lines in this file, so it is not realistic to go through by hand and modify anything.

Here is what I have so far.

  IF ($SplitUsr.Count -eq '4')
    {
        $varAction = $SplitUsr[0]
        IF ($varAction -eq 'Logon')
        {
            $varActionx = $SplitUsr[0].Trim()
            $varUser = $SplitUsr[1].Trim()
            $varHostname = $SplitUsr[2].Trim()
            $varTime = $SplitUsr[3].Trim()

            try {$datetime = [dateTime]::Parse("$varTime",([Globalization.CultureInfo]::CreateSpecificCulture('en-GB')))}
            catch [System.Management.Automation.MethodInvocationException]
            {
                $datetime = [dateTime]::Parse("$varTime",([Globalization.CultureInfo]::CreateSpecificCulture('en-US')))
            }

                $objLogon = New-Object PSObject
                $objLogon | Add-Member -Membertype NoteProperty -Name "Entry" -Value $intCount
                $objLogon | Add-Member -Membertype NoteProperty -Name "Logon" -Value '1'
                $objLogon | Add-Member -Membertype NoteProperty -Name "User" -Value $varUser
                $objLogon | Add-Member -Membertype NoteProperty -Name "Hostname" -Value $varHostname
                $objLogon | Add-Member -Membertype NoteProperty -Name "Date" -Value $datetime
                $objList += $objLogon

Unfortunately, this is parsing them into

1. 10 September 2007 09:56:40
2. 10 September 2007 11:26:20
3. 09 November 2007 10:16:27
4. 11 September 2007 12:28:45

You can see that the 3rd example, the one with the American formatting in the raw data, came out as November instead of the 11 September (inverting the 9 and 11).

The same thing is happening all over the place. When I look at the SQL entries for December, here's what I'm getting:

1. 07 December 2007 09:53:33
2. 07 December 2007 11:37:48
3. 12 July 2007 13:25:02
4. 07 December 2007 13:26:38
5. 07 December 2007 15:04:56

You can see that the third one somehow got the 12 and 7 inverted. This is the problem I'm trying to resolve.

Any suggestions?

Edit: A few more samples:

1. Logon;Username;Server01;18/11/2008 11:19:08
2. Logon;Username;Server02;18/11/2008 11:21:46 AM
3. Logon;Username;Server03;18/11/2008 14:28:30
4. Logon;Username;Server04;19/11/2008 09:55:50
5. Logon;Username;Servername;19/11/2008 14:14:09
6. Logon;Username;Servername;19/11/2008 14:19:56
7. Logon;Username;Servername;20/11/2008 12:19:57 PM

Not all the AM/PM indicate American formatting, unfortunately.

Answer 1

Unfortunately, not all the AM/PM indicate American date formats.

Without additional information, you cannot solve your problem, because of inherent ambiguities:

9/11/2007 10:16:27 AM

It is impossible to tell whether this is an en-US (US) timestamp that refers to the 11th day of September (month first), or a en-GB (UK) timestamp that refers to 9th day of November (day first).

Only if either the first or the second component happens to be 13 or higher is en-US or en-GB implied, and only such timestamps would be handled correctly by the try / catch logic in your question.

---

If you provide an additional constraint that all dates must meet, a solution is possible.

For instance, if you know that all dates fall into a given month:

# The month that all log entries are expected to fall into:
$refMonth = 9  # September, for example.

# Create an array of the cultures to use for parsing:
[cultureinfo[]] $cultures = 'en-GB', 'en-US'

'11/9/2007 17:02:15',
'9/11/2007 05:02:44 PM',
'11/9/2007 05:03:01 PM' | ForEach-Object {

   $ok = $false; [datetime] $dt = 0
   foreach ($culture in $cultures) {
     $ok = [datetime]::TryParse($_, $culture, 'None', [ref] $dt) -and $dt.Month -eq $refMonth
     if ($ok) { break }
   }
   if (-not $ok) { Write-Error "Not recognized as a date in the expected month: $_" }
   else { $date } # Output the resulting date.

}

The above yields the following, showing that all dates were parsed as month 9 (September) dates:

Tuesday, September 11, 2007 5:02:15 PM
Tuesday, September 11, 2007 5:02:44 PM
Tuesday, September 11, 2007 5:03:01 PM

Answer 2

This is the "KI" you was speaking of:

$dates = @( '10/09/2007 09:56:40',
            '09/10/2007 11:26:20',
            '10/09/2007 10:16:27 AM',
            '10/09/2007 12:28:45' )

    $cultureUS = [CultureInfo]::CreateSpecificCulture("en-US")
    $cultureEU = [CultureInfo]::CreateSpecificCulture("en-GB")

    $maxDays   = 2  # Max. allowed difference between current date and former date in days

    for( $i = 0; $i -lt $dates.Count; $i++ ) {

        $currentDate = [DateTime]::Parse( $dates[ $i ],$cultureEU )

        if( $i -gt 0 ) {
            $diffPast = New-TimeSpan -Start $lastDate -End $currentDate
        }
        else {
            $diffPast = New-TimeSpan -Start $currentDate -End $currentDate
        }

        if( $diffPast.Days -gt $maxDays ) {

            # check if month of current date is day of last date => culture issue
            if( $currentDate.Day -eq $lastDate.Month -or $currentDate.Month -eq $lastDate.Day ) {
                $currentDate = [DateTime]::Parse( $dates[ $i ],$cultureUS )
            }
        }

        $currentDate

        $lastDate = $currentDate

    }