Date and Time Formatting

I’ve made a few posts about the hassles of dates and time – I just wish we could all agree on a format and stick to it.

If you are dealing with dates in Powershell in a production script where the script and the updates it makes are viewed globally then you have my sympathy.

Hopefully this post will help you solve your problems.

Dealing with dates is awkward because everybody likes to have their own system for writing down dates.  Our cousins in the US use MM/DD/YYYY and we use DD/MM/YYYY.  Other countries use different separators like the period ( Germany ) or a hyphen (Canada).

This is before we get into the different ways to display a date, try this command at a PoSH prompt:

(Get-Culture).DateTimeFormat

This lists all the ways that a date can be displayed.

dateformats

This usually isn’t a problem when getting dates out of an AD attribute as the data is stored as UTC and is in a standard format.  When you extract the information and display it the system does the conversion for you and displays it using the ‘culture’ of your host operating system.

What your OS is cultured?

You can find out what date and time formats your PC is currently using at a PoSH prompt

A cultured PoSH prompt?

$(Get-Culture).Name

In the UK this will return en-GB for English – Great Britain.

Anyway the problem is when you start extracting date strings from CSV files.  Now you need to know if the string 12/02/2016 is the 12th of February or the 2nd of December.

There is no fix for this by the way you just have to know!  You could parse the whole file and just check your assumption that the dates are in UK or US format but there is no guarantee that there are any date strings that violate either assumption.  Any date string that does not have a number above 12 will pass both tests.  Only days 13 and above will reveal the formatting.

Right so lets assume we know that the file has dates in US format.  For our US cousins they can now stop reading as there is nothing for you to do, unless of course the date stings in your file are UK format, then you have exactly the same issue as your UK counterpart.

Excel gets it wrong too so don’t feel bad for your powershell code

Did you know Excel also makes assumptions based on your regional settings and you know what happens when you assume something.     Yup excel will make a complete mess of your data!  Try it if you don;t believe me!  Anyway back to PowerShell.

Converting dates

I’m only going to cover, for now at least the conversion between UK and US dates.  The same principles apply for any conversion.  You need to know the format in the file and the ‘culture’ of the system processing it.  Why do I need the ‘culture’?  Because this can actually be different for the user of any system so rather than assume what it is use Get-Culture and this way your script should in theory work on any system using any date ‘culture’.

These are not the droids you are looking for

In converting my date strings to match my system date I have 3 possible states.

  1. The date string in the file matches my system ‘culture’
  2. The date string is in US and my system is UK – convert from US to UK
  3. The date string is in UK and my system is in US – convert from UK to US

Once we know which action we need to do you just use a substring statement to rearrange the date string.

Oh one other thing I’m assuming we are using a 4 digit year in this example and every day  and month is using 2 characters.

if ( ( ( $dateFormat -eq “US” ) -and ( $(Get-Culture).Name -eq “en-US” ) ) -or ( ( $dateFormat -eq “GB” ) -and ( $(Get-Culture).Name -eq “en-GB” ) ) )

# leave as is

{$Users = Import-Csv $File -Encoding “UTF7” | Select-Object ‘ID’,’Date’}

elseif ( ( $dateFormat -eq “US” ) -and ( $(Get-Culture).Name -eq “en-GB” ) )

{$Users = Import-Csv $File -Encoding “UTF7” |
Select-Object ‘ID’,@{Name=’Date’;Expression={“$($_.’Date’.substring(3,2))/$($_.’Date’.substring(0,2))/$($_.’Date’.substring(6,4))”}}}

elseif ( ( $dateFormat -eq “GB” ) -and ( $(Get-Culture).Name -eq “en-US” ) )
{$Users = Import-Csv $File -Encoding “UTF7”  |
Select-Object ‘ID’,@{Name=’Date’;Expression={“$($_.’Date’.substring(0,2))/$($_.’Date’.substring(3,2))/$($_.’Date’.substring(6,4))”}}}

I think I got that right 🙂  anyway all you need to do is to split up that date sting to suit your culture.  The code above shows you how so now you should be able to write your own conversions.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.