datetime and string conversion problem
-
Hi Everyone
Im pulling some data out of one database with a query that looks like:
$localquery = "SELECT [SessionIdTime],[SessionIdSeq],[InviteTime],[FromUri],[ToUri],[FromUriType],[ToUriType]..........
$data = Invoke-Sqlcmd -serverinstance "localhost" -query $localquery
If I look at the object type of the "SessionIdTime in the $data array it is a date time object and contains the milliseconds that have been pulled from the database.
I then put this info into another database (Dont ask :D):
foreach ($d in $data){
$qry = "insert into $table VALUES ('$($d.SessionIdTime)', '$($d.SessionIdSeq)', '$($d.InviteTime)', '$FromUri', '$ToUri', '$($d.FromUriType)'.........
Invoke-Sqlcmd -Database $database -ServerInstance $server -Query $qry
}
The problem comes in where during this insert the value is put in as a string and the milliseconds are lost which are key to this requirement.
I have tried to change the this and do a check as follows:
foreach ($d in $data){
$check = $d.SessionIdTime | get-date
$qry = "insert into $table VALUES ('$check', '$($d.SessionIdSeq)', '$($d.InviteTime)', '$FromUri', '$ToUri', '$($d.FromUriType)'.........
Invoke-Sqlcmd -Database $database -ServerInstance $server -Query $qry
}
I then get the error:
Invoke-Sqlcmd : Conversion failed when converting datetime from character string.
The value in the array is definitely a date object and there is no other code between this.
The field is a datetime field in the destination database.
Your assistance with this is much appreciated.
-
So this took me a bit to work out... TL;DR you can't use Invoke-SqlCmd to move data where milliseconds matters in your datetime data. The reasoning behind this as far as I know is because of the .NET. There is even a full MDSN article on dealing with it when you want to get milliseconds in your output.
In an example to even make PowerShell output the milliseconds you have to set the format, which this uses the .NET format noted in the article above:
Get-Date -format 'yyyy-MM-dd hh:m:s.fff'
This is the T-SQL I am using for a test case:
USE [tempdb];
GO
IF OBJECT_ID('dbo.TestTable') IS NOT NULL
DROP TABLE TestTable;
GO
IF OBJECT_ID('dbo.TestTable2') IS NOT NULL
DROP TABLE TestTable2;
GO
CREATE TABLE TestTable (DateTimeTest datetime);
GO
CREATE TABLE TestTable2 (DateTimeTest datetime);
GO
INSERT INTO TestTable
SELECT GETDATE() AS DateTimeTest
WAITFOR DELAY '00:00:05'
INSERT INTO TestTable
SELECT GETDATE() AS DateTimeTest
SELECT * FROM TestTable
/* Output */
/*
DateTimeTest
2015-12-29 21:46:23.960
2015-12-29 21:46:28.960
*/
So in PowerShell if I want to get that format to output I have to do something like this:
$qry = "SELECT DateTimeTest FROM TestTable"
$data = Invoke-Sqlcmd -ServerInstance localhost\sql12 -Database tempdb -Query $qry;
foreach ($d in $data) {
Get-Date -Date $d.DateTimeTest -format 'yyyy-MM-dd hh:mm:ss.fff'
}
If you make that adjustment to force the millisecond to be passed into your query though Invoke-Sqlcmd will barf with an error like "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value". I never could see if there was any workaround for this error.
If you are moving data just use the .NET namespace System.Data.SqlClient as it makes things much easier to manage. I had to mess with datetime values pulling AD data into a database for a customer recently. In that situation I had to use RBAR to load the data; I seemed to have hit some limitation in PowerShell version 2.0 and how it deals with null values in .NET. If you happen to be in PowerShell version 3.0 or higher you can utilize the BulkCopy method (aka BULK INSERT). I wrote a script to build out StackExchange databases in PowerShell[/url] and utilize BulkCopy to load that data, so if you want you can check out the function Load-SeArchiveToSql to see how I utilize it.
Back to your situation, I ended up just creating a function to handle the RBAR for my customer, so based on the T-SQL showed above this should solve your problem:
Import-Module SQLPS
$qry = "SELECT DateTimeTest FROM TestTable"
$data = Invoke-Sqlcmd -ServerInstance localhost\sql12 -Database tempdb -Query $qry;
$script:dateTimeTest
$sqlConn = New-object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = "Server=localhost\sql12;Database=tempdb;Integrated Security=True"
$sqlConn.Open()
function Insert-DataRowByRow {
[cmdletbinding()]
param(
[Data.SqlClient.SqlConnection]$sqlcn,
[string]$query,
[switch]$isObject,
[switch]$isRelation
)
$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
$sqlCmd.Connection = $sqlcn
$sqlCmd.CommandText = $query
$sqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@DateTimeTest",[Data.SqlDbType]::DateTime))) | Out-Null
$sqlCmd.Parameters[0].Value = $dateTimeTest
try{
$sqlCmd.ExecuteScalar()
}
catch{
write-verbose "$($_.Exception.Message) : $query"
}
} #end Insert-DataRowByRow
foreach ($d in $data) {
$dateTimeTest = $d.DateTimeTest
$qry = "INSERT INTO TestTable2(DateTimeTest) VALUES(@DateTimeTest)"
Insert-DataRowByRow -sqlcn $sqlConn -query $qry
}
This code basically parametrizes the query and when you are adding parameters to the "$sqlCmd" object you just set the data type according to what is allowed in the Data.SqlClient.SqlParameter namespace.
Anyway, running the above command gives this output to each test table I used:
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton -
Hi Shawn
Thanks so much for your help once again.
We actually managed to get this working using the same method you first tried - wrote this function:
Function FDate {
param(
[Parameter(Mandatory = $false)] $param
)
#check for null values in the date field. Different to normal null verification
if ($param -eq [System.DBNull]::Value){
$correctdateformat = ""}
else {
$correctdateformat = $param | get-date -format "yyyy-MM-dd hh:mm:ss:fff"}
return $correctdateformat
}
This works fine in our scenario - although initially i had issues when expecting a [DateTime] object for the parameter of the function. After removing this it worked. I never saw the error you are getting for some reason.
The only issue is you are then required to call this function for each field you need formatted in this way:
foreach ($d in $data){
$SessionIdTime = FDate($d.SessionIdTime)
qry = "insert into $table VALUES ('$SessionIdTime', '$($d.SessionIdSeq)', '$InviteTime', '$FromUri', '$ToUri'.......
etc }
Your method looks much more efficient.
Thanks again for your help
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply