Convert UK to US date format in CSV

PowerShell Help
1

Hi,

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?

Thanks!
Nick

2 
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!

http://msdn.microsoft.com/en-us/library/system.globalization.datetimeformatinfo(VS.85).aspx

3

Thanks Will,
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 :slight_smile:

4

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
£> $d

25 December 2014 00:00:00

You could do something like this

£> $sd = ‘25/12/2014’ -split ‘/’
£> $sd
12
25
2014
£> $d = Get-Date -Day $sd[0] -Month $sd[0] -Year $sd[2]
£> $d

25 December 2014 18:25:56

5

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)
6

Whoops! I totally misread your original post. My bad Nick. :slight_smile: