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!