PowerShell stupid date formatting tricks, again

[Kurt Buff]

I've got a listing from one of my colleagues - csv report from AAD of last login dates, and am trying to parse the dates into a format that's sortable.

Here's what I see - it's seems to me that the single-digit months (and single-digit days) are screwing this up, so it's possible that using ParseExact is not the correct approach here.

Any thoughts?

Kurt

Import-Csv C:\temp\AADLastUserLogin.csv | ForEach-Object {
    # if the value on 'LastSignInDate' property is empty or white space
    if([string]::IsNullOrWhiteSpace($_.LastSignInDate)) {
        # return this object as-is without any modifications,
        # and go to the next item
        return $_
    }

    # here we assume the property is populated, so we can update it
    $_.LastSignInDate = [datetime]::ParseExact($_.LastSignInDate, 'MM/dd/yyyy hh:mm:ss tt', [cultureinfo]::InvariantCulture).ToString('yyyy-MM-dd')
    $_
} | Select-Object DisplayName, LastSignInDate


Sample Data:
UserPrincipalName DisplayName LastSignInDate
Dan....@example.com Dan Cole 10/27/2023 10:33:28 PM
Adrien....@example.com Adrien Barbeau 1/9/2024 8:15:48 PM
Robert...@example.com Robert Smith 11/1/2023 3:04:05 PM

For Dan Cole, I see good return, with this data:
Dan Cole 2023-10-27

For Adrien Barbeau, I get this data and error:
Adrien Barbeau                     1/9/2024 8:15:48 PM
Exception calling "ParseExact" with "3" argument(s): "String was not recognized as a valid DateTime."
At line:10 char:5
+     $_.LastSignInDate = [datetime]::ParseExact($_.LastSignInDate, 'MM ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : FormatException

For Robert Smith, I get this data and the same error:
Robert Smith          11/1/2023 3:04:05 PM
Exception calling "ParseExact" with "3" argument(s): "String was not recognized as a valid DateTime."
At line:10 char:5
+     $_.LastSignInDate = [datetime]::ParseExact($_.LastSignInDate, 'MM ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : FormatException

[Andrea 'ML' Suatoni]

On 7/3/2024 22:19, Kurt Buff wrote:

Here's what I see - it's seems to me that the single-digit months (and single-digit days) are screwing this up, so it's possible that using ParseExact is not the correct approach here.

Any thoughts?

I'd say 'M/d/yyyy h:mm:ss tt' (single M, single d, single h) would catch both single and double digits.

Andrea

[Michael B. Smith]

Let PowerShell do the work for you.

 

$_.LastSignInDate = ( $_.LastSignInDate -as [DateTime] ).ToString( 'yyyy-MM-dd' )

--
You received this message because you are subscribed to the Google Groups "ntpowershell" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ntpowershell...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ntpowershell/CADy1Ce5%2B0ifdQ8V2MZcPTz37MshH%2BM6o%3D2RY24kj%2B_bWxNkuYQ%40mail.gmail.com.

[Kurt Buff]

Thank you kind sir. That worked exactly as needed.

Why is it that it's often simpler than than I think?

Kurt

To view this discussion on the web visit https://groups.google.com/d/msgid/ntpowershell/97a137a50ff64733bb8030fee2d260e4%40smithcons.com.

[Michael B. Smith]

tl/dr: it’s not just you 😊

 

I find it a lot when I’m looking at my older PS. It’s rather surprising to remember that PowerShell (nee’ Monad) has been around since 2006.

 

It takes some time to learn (and learn to use) all of it. There are parts of it that I still rarely use (e.g., filters and classes) that would probably make some of my modern code much simpler.

To view this discussion on the web visit https://groups.google.com/d/msgid/ntpowershell/CADy1Ce7wpO9wKRM1VDa7pxebTWSBTghLJifT0E6CfvvZR5CT_Q%40mail.gmail.com.