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:
[sourcecode language=”text”]
:
<OrderDate>2001-07-01T00:00:00-04:00</OrderDate>
:
[/sourcecode]
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:
[sourcecode language=”powershell”]
$utc = "2001-07-01T00:00:00-04:00";
[datetime] $converte_DateTime = $utc;
## – Resulting in:
#-> Sunday, July 1, 2001 12:00:00 AM
[/sourcecode]
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:
[sourcecode language=”sql”]
–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
[/sourcecode]
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:
[sourcecode language=”powershell”]
## – the following routine will correct UTC datetime as string:
[xml] $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’);
[/sourcecode]
The results after convert the DateTime to the new XML DateTime values fix will look like this:
[sourcecode language=”text”]
:
<OrderDate>7/1/2001 12:00:00 AM</OrderDate>
:
[/sourcecode]
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!