Convert string to DateTime in Powershell from CSV

Question

I'm having the weirdest and most annoying problem with dealing with a string here that I need to convert to DateTime.

I'm doing the exact same thing from 2 different CSV files - it works perfectly on the first one, keeps returning an error on the second one.

$userDateOut = Get-Date $sourceLine.Date_OUT -Format "dd/MM/yyyy"
$userDateOut = ($userDateOut -as [datetime]).AddDays(+1)
$userDateOut = Get-Date $userDateOut -Format "dd/MM/yyyy"

In the first CSV, Date_OUT is just 31/12/2021 for example, and in the second one it's 31/12/2021 0:00:00.

So before the 3 lines to create $userDateOut, I do

$userDateOut = $sourceLine.Date_OUT.SubString(0,10)

Which makes me end up with the same type of variable as with the first CSV

PS C:\Windows\system32> $userDateOut = $sourceLine.Date_Out.Substring(0,10)
PS C:\Windows\system32> $userDateOut
31/12/2021
PS C:\Windows\system32> $userDateOut.GetType()

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     String                                   System.Object

However, with this variable, I'm getting

PS C:\Windows\system32> $userDateOut = Get-Date $userDateOut -Format "dd/MM/yyyy"
Get-Date : Cannot bind parameter 'Date'. Cannot convert value "31/12/2021" to type "System.DateTime". Error: "String was not recognized as a valid DateTime."
At line:1 char:25
+ $userDateOut = Get-Date $userDateOut -Format "dd/MM/yyyy"
+                         ~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Get-Date], ParameterBindingException
    + FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Microsoft.PowerShell.Commands.GetDateCommand

And I don't know why... Can someone help ?

Santiago Squarzon · Accepted Answer · 2024-07-05 13:52:16Z

4

-Format just converts [datetime] to a [string] - it doesn't influence parsing of input strings in any way.

For that, you need [datetime]::ParseExact():

$dateString = '31/12/2021'
# You can pass multiple accepted formats to ParseExact, this should cover both CSV files
$inputFormats = [string[]] @(
    'dd/MM/yyyy H:mm:ss'
    'dd/MM/yyyy'
)

$parsedDatetime = [datetime]::ParseExact($dateString, $inputFormats, $null, [System.Globalization.DateTimeStyles]::None)

You can then use Get-Date -Format to convert it back to an intended output format if needed:

Get-Date $parsedDatetime -Format dd/MM/yyyy

edited Jul 5, 2024 at 13:52

Santiago Squarzon

65.5k5 gold badges26 silver badges59 bronze badges

answered Sep 11, 2020 at 15:45

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Michel Balencourt Over a year ago

By googling I had found DateTime ParseExact but only for C# and couldn't figure it out for Powershell. Problem solved !

Mathias R. Jessen Over a year ago

@MichelBalencourt In that case you might find this answer useful as well! :)

Collectives™ on Stack Overflow

Convert string to DateTime in Powershell from CSV

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related