This topic contains 5 replies, has 4 voices, and was last updated by
December 23, 2014 at 9:40 am #21378ParticipantPoints: 0Rank: Member
I have a CSV file with 10 columns (the last column is the date)
Currently the date is DD/MM/YYYY and I need to change this to the US format which is MM/DD/YYYY]
What's the best way to do this? Should I slice the day month and year into a variable and then reorder it or is there an easier way?
December 23, 2014 at 10:14 am #21382KeymasterPoints: 0Rank: Member
Get-Date -Format dd/mm/yyyy
And just because that answer seems incredibly short – there's actually an article linked from the Get-Date help on TechNet that gives you a full list of format types you can use. Enjoy!
December 23, 2014 at 10:22 am #21383ParticipantPoints: 0Rank: Member
Sorry I should have probably been clearer – the dates in the csv file are different dates on each line and none of them are today's date so I need to get the date already in the file and then convert, not just put today's date 🙂
December 23, 2014 at 10:28 am #21384ParticipantPoints: 0Rank: Member
Unfortunately there isn't an easy way to do this. Your CSV file holds the date as a string. You need to convert that to a date. If you try this
£> $sduk = '25/12/2014'
£> $d = [datetime]$sduk
Cannot convert value "25/12/2014" to type "System.DateTime". Error: "String was not recognized as a valid DateTime."
At line:1 char:1
+ $d = [datetime]$sduk
+ CategoryInfo : InvalidArgument: (:) , RuntimeException
+ FullyQualifiedErrorId : InvalidCastParseTargetInvocationWithFormatProvider
where the date is 25 December 2014 in UK format it will fail.
.NET expects the string in US format
£> $sdus = '12/25/2014'
£> $d = [datetime]$sdus
25 December 2014 00:00:00
You could do something like this
£> $sd = '25/12/2014' -split '/'
£> $d = Get-Date -Day $sd -Month $sd -Year $sd
25 December 2014 18:25:56
December 23, 2014 at 10:37 am #21386MemberPoints: 0Rank: Member
Casting a string to a datetime assumes invariant (basically US) format, but calling [datetime]::Parse() allows you to specify a culture (as does the ToString() method on datetime objects). For example:
$ukString = '25/12/2014' $ukCulture = [Globalization.CultureInfo]'en-GB' $usCulture = [Globalization.CultureInfo]'en-US' $datetime = [datetime]::Parse($ukString, $ukCulture) $usString = $datetime.ToString('d', $usCulture)
December 23, 2014 at 11:52 am #21394KeymasterPoints: 0Rank: Member
Whoops! I totally misread your original post. My bad Nick. 🙂
The topic ‘Convert UK to US date format in CSV’ is closed to new replies.