Getting a Sort to work on a Date Object (but after converting it?)

Welcome Forums General PowerShell Q&A Getting a Sort to work on a Date Object (but after converting it?)

Viewing 2 reply threads
  • Author
    Posts
    • #227821
      Participant
      Topics: 8
      Replies: 15
      Points: 76
      Rank: Member

      Hi Folks,

      Having a trouble getting my date object to sort correctly.

      I have a very simple script that will just list the certificates on a server. But, the expiry date is US format and I want to force it to European date format and sort so that those expiring are listed first.

      So, if I run the script as follows, it sorts correctly – on the US date format

       $remotemesg2 = 
       foreach ($cert in Get-ChildItem Cert:\LocalMachine\My) {
         [PSCustomObject]@{
         Name = $cert.FriendlyName
         Expires = $cert.NotAfter
         IssuedBy = (($cert.Issuer).Split("=")[1]).Split(",")[0]
          }
       }
      $RemoteMesg2 = $RemoteMesg2 | Select-Object Name,Expires,IssuedBy | Sort-Object Expires

      And it outputs as follows, and the sort works.

      Name Expires IssuedBy 
      ---- ------- -------- 
      abc-dev.contoso.com 10/28/2017 10:41:56 AM Contoso Certification Authority - L1K
      xyz-tst.contoso.com 9/13/2018 12:38:46 PM  Contoso Basic Assurance TEST CA 
      efg-dev.contoso.com 10/16/2018 11:39:18 AM Contoso Certification Authority - L1K

      I’ve tried a few ways to get this part into dd/mm/yyyy format:

      Expires = $cert.NotAfter

      so, I played around with different variations of the following:

      Expires = $cert.NotAfter | Get-Date -Uformat "%d-%m-%Y" | Sort-Object { [datetime]}

      Thinking that it would pipe the original format into Get-Date to the Uformat – and then sort it as a Real Date, but it doesn’t – it sorts as a string, and just sorts on the day value like ascending integers.

      xyz-tst.contoso.com 13/09/2018 Contoso Basic Assurance TEST CA 
      efg-dev.contoso.com 16/10/2018 Contoso Certification Authority - L1K
      abc-dev.contoso.com 28/10/2017 Contoso Certification Authority - L1K

      I guess I don’t want to convert the date to UK format, I just want to format it as UK format and sort correctly…It’s driving me nuts, I’m sure it’s a simple answer for sure… and maybe Olaf will come to my rescue? 😉

      Thanks, Alex74

       

       

    • #227836
      Participant
      Topics: 12
      Replies: 1622
      Points: 2,560
      Helping Hand
      Rank: Community Hero

      Sorting dates either needs to be a DateTime object or a sortable date. Sorting strings like that can have some odd behavior. Here is another way to do it that should be a bit cleaner:

      Get-ChildItem Cert:\LocalMachine\My |
      Sort NotAfter |
      Select @{Name='Name';Expression={$_.FriendlyName}},
             @{Name='Expires';Expression={Get-Date -Date $_.NotAfter -Uformat "%d-%m-%Y"}},
             @{Name='IssuedBy';Expression={(($_.Issuer).Split("=")[1]).Split(",")[0]}}
      
    • #227905
      Participant
      Topics: 8
      Replies: 15
      Points: 76
      Rank: Member

      Rob, you are a squire and a gentleman, thanks for the explanation and the solution – works just great!

Viewing 2 reply threads
  • You must be logged in to reply to this topic.