Filling DataSet using SqlDataAdapter is changing the date format

Question

I've searched all over the web and am unable to find a solution/guide for my problem.

I'm using the below bit of script its part of a larger script to export multiple SQL tables into CSVs. It fills a dataset with data from an SQL table.

The problem I have is in mainly in relation to datetime settings. For example, If I were to export the SQL table using the export wizard into a CSV file. The date appears exactly like it does in SQL e.g. "2014-05-23 07:00:00.0000000" or "2014-05-23".

However when I use my script it changes the format of the datetime to "23/05/2014 07:00:00" or "23/05/2014 00:00:00". I believe this has something to do with the culture settings of my machine/powershell session.

cls

# Declare variables for connection and table to export
$Server     = 'server'
$Database   = 'database'
$Folder     = 'D:\Powershell Scripts\01_Export From SQL\Test Folder'
$Tablename1 = 'test'
$Tablename2 = ''

# Delcare Connection Variables
$SQLconnection = New-Object System.Data.SqlClient.SqlConnection
$SQLconnection.ConnectionString = "Integrated Security=SSPI;server=$Server;Database=$Database"

# Delcare SQL command variables
$SQLcommand = New-Object System.Data.SqlClient.SqlCommand 
$SQLcommand.CommandText = "SELECT [name] from sys.tables where [name] like '%$Tablename1%' and [name] like '%$Tablename2%' order by [name]"
$SQLcommand.Connection = $SQLconnection 

# Load up the Tables in a dataset
$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter 
$SQLAdapter.SelectCommand = $SQLcommand 
$DataSet = New-Object System.Data.DataSet 
$null = $SqlAdapter.Fill($DataSet)
$SQLconnection.Close()

"Time to Export`tRecords   `tFile Name"
"--------------`t-------   `t---------"

foreach ($Table in $DataSet.Tables[0])
{

    $stopwatch = [system.diagnostics.stopwatch]::StartNew()

    $FileExtractUTF8 = "$Folder\FromPSUTF8_$($Table[0]).csv"
    $SQLcommand.CommandText = "SELECT * FROM [$($Table[0])]"

    $SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SQLAdapter.SelectCommand = $SQLcommand 

    $DataSet = New-Object System.Data.DataSet 
    $Count = $SQLAdapter.Fill($DataSet)              
    $SQLconnection.Close() 

    $DataSet.Tables[0]  | Export-Csv $FileExtractUTF8 -NoTypeInformation -Encoding UTF8        

    $stopwatch.stop()

    $Time = "{0}" -f $stopwatch.Elapsed.ToString('mm\:ss\.fff')

    “{0,-14}`t{1,-10}`t{2}” -f $Time,$Count,$Table.name
}

The main goal is to export the data from SQL into a flat file with the data appearing exactly as it would if I used the export wizard.

Answer 1

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. ;-)

Answer 2

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 null String format. 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.