Powershell Incorrect Date Sorting

csvpowershellpowershell-2.0

I have a CSV file that contains ID's with various different. With a little help I've been able to trim the file to the ID with the oldest date and it works great…most of the time. I have several records that seems to return the newest date while the other's return the oldest and I can't figure out why. It's always the same records that that this happens on.

Here is a sample from my input CSV:

Number  File Date
OU00067949, 6/14/2012,
OU00067949, 6/19/2012,
OU00067949, 11/15/2012,
OU00067949, 1/30/2013,
OU00080302, 1/3/2013,
OU00080302, 1/3/2013,
OU00080302, 12/28/2012,
OU00080302, 12/28/2012,
OU00080302, 12/27/2012,
OU00080302, 12/27/2012,
OU00080281, 2/14/2013,
OU00080281, 1/30/2013,
OU00080281, 1/16/2013,
OU00080319, 12/27/2012,
OU00080319, 12/27/2012,

And here is the output: 

Number  File Date
OU00067949, 1/30/2013,
OU00080302, 1/3/2013,
OU00080281, 1/16/2013,
OU00080319, 12/27/2012,

I am expecting the date of 6/14/2012 for OU00067949 as this is the oldest date. Instead I'm getting 1/30/2013. The same is happening for OU00080302, yet the others are correct. Any clue what could be causing this?

This is all there is to the script:

Import-CSV $ImportFile | Group-Object "Number" | ForEach-Object { $_.Group | Sort-Object "File Date" | Select -First 1} | Export-CSV $ExportFile -NoTypeInformation

Any help would be greatly appreciated.

Best Answer

Looks like it's trying to sort dates as strings. Try this instead:

Import-CSV $ImportFile | Group-Object "Number" | ForEach-Object { $_.Group | Sort-Object { $_."File Date" -as [datetime] } | Select -First 1} | Export-CSV $ExportFile -NoTypeInformation

so this:

Sort-Object "File Date"

became this:

Sort-Object { $_."File Date" -as [datetime] }

Credit goes here:

Related Topic