Powershell – Writing Date formats – Regional Compatibility

I was working on a PowerShell script to pull users from the Active Directory based on a certain condition.

I was able to pull the data very well and export it to an output CSV file.

But, the whenCreated Attribute which is natively pulled from AD is in the

Format DD-MM-YY-HH-SS.

When you send this csv file to users in different geographies which use different Regional settings will see the date in a different format.

For example, the native whenCreated attribute will have a value 06-07-2015 (6th July).

Actual Value taken from CSV ( opened in a notepad)

06-07-2015 05:08:56

And when you send the file containing these values to someone in US, they will see the date as 07/06/2015 (M-D-Y format) which excel transforms the dates it according to the regional settings.The date will be read as  (07/06/2015)(7th June) .

As a simple work-around, if we force the date to be written in the Y-M-D format, the file will show the same date across all regions despite the regional settings, excel will perfectly align the date conveying the correct information.

This is when you have a very big infrastructure and when you use ADSI to query the AD.

Powershell native cmdlets sometimes take a lot of time to process complex filters, so its best to pull the entire directory dataset in a recordset based on some simple filters and then process the data in local memory.

Example code:

#Save the date in a variable object.

$date_ = (($obj.properties.whencreated[0]))

#the $date_ is a DateTime object, and we can extract the year, month and day and assign it to a string object.

$whenCreated=[string]([String]$date_1.year+"-"+[string]$date_1.month+"-"+[String]$date_1.day)

$date_1.year > extracts the year

[String]$date_1.year > Type Casts the year object to a string

The same is done for month and day.

Then the entire thing is concatenated into a string and then typecasted into a string again.

[String]([String]$date_1.year+"-"+[string]$date_1.month+"-"+[String]$date_1.day)

The $whenCreated object when written to a CSV file will output in Y-M-D format.

For example: 20015-06-07