powershell convert csv column to datetime

Question

I have a CSV with a 'datetime' column in this format- "11/13/2022 4:30:00 PM". How do I convert that string to a [datetime] type?

I apologize Im having a tough time with this one.

$CSV = Import-Csv "this.csv" 
    
$formattedcsv = $CSV | Select-Object *, @{
        Name        = 'DateTime'
        Expression  = {
            (("MM/dd/yyyy HH:mm") -as [datetime])
        }
    }

try Expression = {[datetime]::ParseExact($_.datetime, "mm/dd/yyyy HH:mm", [cultureinfo]::CurrentCulture)} — frankM_DN
– frankM_DN, Commented Nov 17, 2022 at 13:23
This won't work because [1] for the month part you need MM (lowercase is for minutes) and [2] the example shows there is only one Hour digit (12-hour clock, so that needs to be h, not HH 24-hour clock) plus [3] it uses AM/PM designator you did not include — Theo
– Theo, Commented Nov 17, 2022 at 13:31
import-csv this.csv | % { $_.Datetime = [datetime]$_.Datetime; $_ } — js2010
– js2010, Commented Nov 17, 2022 at 13:45

Theo · Accepted Answer · 2022-11-17 13:48:16Z

2

The format you show ("11/13/2022 4:30:00 PM") uses the 12-hour clock and has AM/PM designators.

Use this instead:

$dateFormat = 'MM/dd/yyyy h:mm:ss tt'
$formattedcsv = $CSV | 
Select-Object *, 
              @{Name = 'DateTime'
                Expression = {[datetime]::ParseExact($_.datetime, $dateFormat, [cultureinfo]::InvariantCulture)}
              } -ExcludeProperty datetime

edited Nov 17, 2022 at 13:48

answered Nov 17, 2022 at 13:25

Theo

61.9k8 gold badges28 silver badges48 bronze badges

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

3 Comments

iceman Over a year ago

Ah Thank you Theo I appreciate your time.

js2010 Over a year ago

I get "property datetime already exists". Maybe I don't understand the question.

Theo Over a year ago

@js2010 Sorry, forgot about that.. Fixed now

Collectives™ on Stack Overflow

powershell convert csv column to datetime

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related