Changing default DateTime format within a script

Within your DataSet / DataTable, the date exists as a [DateTime] type. When you export to CSV it needs to be converted to a string so it can be written to file. As you have observed, the default string conversion gives an output such as:

[PS]> (get-date).ToString()
21/03/2017 17:52:26

The format of this string is specified (as you worked out) by your specific globalization "culture". It appears to be a concatenation of the ShortDatePattern and LongTimePattern properties of the DateTimeFormat.

There are plenty of posts from people who have tried and failed to change the culture of their current session (i.e. the running PS host)...

• why-does-powershell-always-use-us-culture-when-casting-to-datetime

...but it may be possible to change the globalization culture within your script using a mechanism such as the ones described here:

• powershell-changing-the-culture-of-current-session
• Using-Culture function

I suspect you should be able to use the Using-Culture example to wrap the Export-Csv line in your script.

But what culture to use?

So, you might now be able to set a specific culture, but do any of the pre-existing cultures use ISO8601 (sortable) dates? Or more specific formats? It seems not, so you have to make your own!

In short, you need to clone an existing CultureInfo and update the DateTimeFormat , specifically (at least) the ShortDatePattern to be what you want. Here is an example that I hope puts you on the right path. There are two functions, one which clones an existing CultureInfo and one which runs a command ( Get-Date ) with the new culture set for that thread.

function New-CultureInfo {
  [CmdletBinding()]
  [OutputType([System.Globalization.CultureInfo])]
  param(
    [Parameter()]
    [System.Globalization.CultureInfo]
    $BaseCulture = [System.Globalization.CultureInfo]::InvariantCulture
  )

  $CultureInfo = ($BaseCulture).Clone()

  $CultureInfo.DateTimeFormat.ShortDatePattern = "yyyy'-'MM'-'dd"
  $CultureInfo.DateTimeFormat.LongTimePattern = "HH:mm:ss.fff"

  return $CultureInfo
}

function Test-DateFormat {
  [CmdletBinding()]
  [OutputType([System.DateTime])]
  param(
    [Parameter(Mandatory)]
    [System.Globalization.CultureInfo]
    $Culture
  )

  [System.Threading.Thread]::CurrentThread.CurrentUICulture = $Culture
  [System.Threading.Thread]::CurrentThread.CurrentCulture = $Culture

  (Get-Date).ToString()
}

Example use:

[PS]> $NewCulture = (New-CultureInfo)

[PS]> Test-DateFormat $NewCulture
2017-03-21 19:08:34.691

Now, I haven't been able to run this against an example close to the SQL problem in the OP, but I've had fun working this all out. ;-)

Great description by Charlie. My problem is that I wanted to change the default ToString to output an ISO datetime that contains a T separator between date and time instead of a space. TL;DR - it's not possible.

I'm more from the Java world than MS but had to write a script to export db CSVs and here's my investigation in case anyone else is interested in how the format is built. I dug into the source code to see how ToString works on DateTime .

According to the DateTime class it will defer to DateTimeFormat https://referencesource.microsoft.com/#mscorlib/system/datetime.cs,0a4888bea7300518

public override String ToString() {
    Contract.Ensures(Contract.Result<String>() != null);
    return DateTimeFormat.Format(this, null, DateTimeFormatInfo.CurrentInfo);
}

This will eventually call into Format method with a nullStringformat . This causes the block below to be called which basically specifies the "G" format to use for the date/time. https://referencesource.microsoft.com/#mscorlib/system/globalization/datetimeformat.cs,386784dd90f395bd

if (offset == NullOffset) {
    // Default DateTime.ToString case.
    if (timeOnlySpecialCase) {
            format = "s";
    }
    else {
        format = "G";
    }
}

This will eventually make a call to get the current Culture's DateTimeInfo object that has an internal String pattern that cannot be overridden. It lazily sets this so that it doesn't have to concatenate and there is no way to override it. As Charlie pointed out, it always concatenates ShortDatePattern and LongTimePattern with a space separator. https://referencesource.microsoft.com/#mscorlib/system/globalization/datetimeformatinfo.cs,799bd6e7997c4e78

internal String GeneralLongTimePattern {
    get {
        if (generalLongTimePattern == null) {
            generalLongTimePattern = ShortDatePattern + " " + LongTimePattern;
        }
        return (generalLongTimePattern);
    }
}

So there you have it. Hope it helps the next person know why/how and that it's not possible to override the overall default format - just the date and the time components.