PowerShell and T-SQL converting XML UTC DateTime value to DateTime string

Following my previous blog (“PowerShell – SQL Server SMO has XML and XSD Schema method“) I continue to use SMO to generate my XML files, I encountered that my SQL datetime field is converted to a UTC DateTime string. This gave me some work to get it back to a DateTime type. I had the requirement to provide my source file in XML format.

I realized using SMO WriteXml() method will write the DateTime type to a UTC DateTime string format in the XML file resulting in something like this:

:
<OrderDate>2001-07-01T00:00:00-04:00</OrderDate>
:

Unfortunate, trying to pass this value to a SQL DateTime field is tricky. I look around the internet and there’s plenty of examples of UTC but not in this format “2001-07-01T00:00:00-04:00”.   So, I ended up using PowerShell to ease off the pain.

Here’s an example of how PowerShell can easly convert UTC DateTime string to a regular System.DateTime type:

$utc = "2001-07-01T00:00:00-04:00";
[datetime] $converte_DateTime = $utc;

## - Resulting in:
#->  Sunday, July 1, 2001 12:00:00 AM

That was easy. Right! But the issue is when we want to pass this value into a SQL DateTime type. Then, here’s a possible solution now that I got PowerShell to convert my UTC string to string DateTime output.

Using the string generated from PowerShell, as a proof-of-concept, I can test this value in the following T-SQL script:

--now using t-SQL to convert a UTC string to a standard datetime type value:
Declare @stringFromPowerShellDateTime nvarchar(50)
Declare @fixDate nvarchar(50)
Declare @cleanup int
Declare @dtDateTime DateTime

Set @stringFromPowerShellDateTime = 'Sunday, July 1, 2001 12:00:00 AM'
Set @cleanup = (charindex(',', @stringFromPowerShellDateTime, 1))
Set @fixDate = (Substring(@stringFromPowerShellDateTime, (@cleanup+1), len(@stringFromPowerShellDateTime)))
Set @dtDateTime =  (Convert(DateTime, @fixDate, 101))

Select @dtDateTime

-- - Resulting in:
- -> 2001-07-01 00:00:00.000

The end result of this SQL script is to be able to create my SQL DateTime type from the PowerShell converted UTC-to-DateTime-String.

Now I need go back to my first XML file, and update my DateTime elements values with the converted UTC to string format. Here’s where I go back PowerShell to fix my XML file:

## - the following routine will correct UTC datetime as string:
 $xmlfile = Get-Content 'C:\PowerShellScripts\ADWtblOrders1.xml';
foreach($xItem in ($xmlFile.newdataset.table))
{
	[string] $dt = ([DateTime] $xItem.OrderDate).ToString();
	$xItem.OrderDate = $dt;
};
$xmlfile.save('C:\PowerShellScripts\ADWtblOrders1_New.xml');

The results after convert the DateTime to the new XML DateTime values fix will look like this:

:
<OrderDate>7/1/2001 12:00:00 AM</OrderDate>
:

Now, this DateTime values can be consume in SQL Server without any issues. Notice that I’m using the “xml” Accelerator which has a *.Save() method so you can save your changes to file .

I think you got the pieces necessary to use PowerShell, XML, SMO, and T-SQL.

Don’t be afraid!