It tried to convert following time in Power Shell Script '2022-01-02T11:43:23.100Z' using Convert Function, and Cast. The tried same in SSMS and passed. Both SSMS and Power shell were connect to same instance of SQL Server 14.0.1000.
Error message indicates in Power shell it is failing in SQLCleint. I download the latest released version of SQLserver cmdlet last week.
in SSMS
SET @Abc = CAST('2022-01-02T11:43:23.100Z' AS datetime)
SET @Abc = CONVERT(DATETIME, '2022-01-02T11:43:23.100Z')
This works in Power Shell
$selectQuery = "SELECT * FROM [dbo].[Message] where svn = $svn AND RxDate_UTC = CAST('$date' AS datetime)"
Invoke-Sqlcmd -ServerInstance $Server -Database XXXXX -Query $selectQuery -StatisticsVariable stats
This is failing in Power Shell when in an INSERT command using $insertCommand.ExecuteNonQuery()
VALUES
(CAST(@date AS datetime),
I successfully loaded 20,173 rows into data base before error occurred. I'm loading data from CSV files into the database.
These are the dates that worked in current csv file. Last failed
Date : 2022-01-02T09:20:29.995Z
Date : 2022-01-02T09:20:35.993Z
Date : 2022-01-02T09:20:41.994Z
Date : 2022-01-02T09:20:47.994Z
Date : 2022-01-02T09:20:53.993Z
Date : 2022-01-02T09:20:59.993Z
Date : 2022-01-02T09:21:11.993Z
Date : 2022-01-02T11:41:29.999Z
Date : 2022-01-02T11:41:35.999Z
Date : 2022-01-02T11:41:41.999Z
Date : 2022-01-02T11:41:47.999Z
Date : 2022-01-02T11:41:53.999Z
Date : 2022-01-02T11:41:59.999Z
Date : 2022-01-02T11:42:11.999Z
Date : 2022-01-02T11:42:17.999Z
Date : 2022-01-02T11:42:23.998Z
Date : 2022-01-02T11:42:29.999Z
Date : 2022-01-02T11:42:35.999Z
Date : 2022-01-02T11:42:41.999Z
Date : 2022-01-02T11:42:47.999Z
Date : 2022-01-02T11:42:53.999Z
Date : 2022-01-02T11:42:59.999Z
Date : 2022-01-02T11:43:23.100Z
Interesting note
The program that create the csv had bug and last date was 2022-01-02T11:43:23.1000
So I added to Power shell code when last character was not a Z to replace with zero. The last row in dates above is the corrected date.
C:\Users\jwarburg\Desktop\StoreCSVSMFLS.ps1 :
ErrorRecord : Exception calling "ExecuteNonQuery" with "0" argument(s): "Conversion failed when
converting date and/or time from character string."
WasThrownFromThrowStatement : False
Message : Exception calling "ExecuteNonQuery" with "0" argument(s): "Conversion failed when
converting date and/or time from character string."
C:\Users\jwarburg\Desktop\StoreCSVSMFLS.ps1 :
ErrorRecord : Exception calling "ExecuteNonQuery" with "0" argument(s): "Conversion failed when
Data : {}
converting date and/or time from character string."
WasThrownFromThrowStatement : False
Message : Exception calling "ExecuteNonQuery" with "0" argument(s): "Conversion failed when
converting date and/or time from character string."
Data : {}
InnerException : System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting date
and/or time from character string.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean
breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
InnerException : System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting date
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand
cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler,
TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior
runBehavior, String resetOptionsString, Boolean isInternal, Boolean
forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior,
RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task,
Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean
describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior,
RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1
completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean
inRetry)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1
completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache,
and/or time from character string.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean
breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand
cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler,
TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior
runBehavior, String resetOptionsString, Boolean isInternal, Boolean
forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior,
RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task,
Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at CallSite.Target(Closure , CallSite , Object )
ClientConnectionId:b62d09f5-a2b0-4076-ac6e-2716779625b0
Error Number:241,State:1,Class:16
TargetSite : Void CheckActionPreference(System.Management.Automation.Language.FunctionContext,
describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior,
System.Exception)
StackTrace : at
System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext
funcContext, Exception exception)
at lambda_method(Closure , Object[] , StrongBox`1[] , InterpretedFrame )
RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1
completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean
inRetry)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1
completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache,
HelpLink :
Source : System.Management.Automation
HResult : -2146233087
At line:1 char:1
+ .\StoreCSVSMFLS.ps1
+ ~~~~~~~~~~~~~~~~~~~
Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
+ CategoryInfo : NotSpecified:
) [Write-Error], WriteErrorException
+ FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,StoreCSVSMFLS.ps1
at CallSite.Target(Closure , CallSite , Object )
ClientConnectionId:b62d09f5-a2b0-4076-ac6e-2716779625b0
Error Number:241,State:1,Class:16
TargetSite : Void CheckActionPreference(System.Management.Automation.Language.FunctionContext,
System.Exception)
StackTrace : at
System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext
funcContext, Exception exception)
at lambda_method(Closure , Object[] , StrongBox`1[] , InterpretedFrame )
HelpLink :
Source : System.Management.Automation
HResult : -2146233087
At line:1 char:1
+ .\StoreCSVSMFLS.ps1
+ ~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified:) [Write-Error], WriteErrorException
+ FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,StoreCSVSMFLS.ps1
C:\Users\jwarburg\Desktop\StoreCSVSMFLS.ps1 :
MyCommand :
BoundParameters : {}
UnboundArguments : {}
C:\Users\jwarburg\Desktop\StoreCSVSMFLS.ps1 :
MyCommand :
BoundParameters : {}
UnboundArguments : {}
ScriptLineNumber : 280
ScriptLineNumber : 280
OffsetInLine : 12
HistoryId : -1
ScriptName : C:\Users\jwarburg\Desktop\StoreCSVSMFLS.ps1
Line : $insertCommand.ExecuteNonQuery()
OffsetInLine : 12
HistoryId : -1
ScriptName : C:\Users\jwarburg\Desktop\StoreCSVSMFLS.ps1
Line : $insertCommand.ExecuteNonQuery()
Error message indicates in Power shell it is failing in SQLCleint. I download the latest released version of SQLserver cmdlet last week.
in SSMS
SET @Abc = CAST('2022-01-02T11:43:23.100Z' AS datetime)
SET @Abc = CONVERT(DATETIME, '2022-01-02T11:43:23.100Z')
This works in Power Shell
$selectQuery = "SELECT * FROM [dbo].[Message] where svn = $svn AND RxDate_UTC = CAST('$date' AS datetime)"
Invoke-Sqlcmd -ServerInstance $Server -Database XXXXX -Query $selectQuery -StatisticsVariable stats
This is failing in Power Shell when in an INSERT command using $insertCommand.ExecuteNonQuery()
VALUES
(CAST(@date AS datetime),
I successfully loaded 20,173 rows into data base before error occurred. I'm loading data from CSV files into the database.
These are the dates that worked in current csv file. Last failed
Date : 2022-01-02T09:20:29.995Z
Date : 2022-01-02T09:20:35.993Z
Date : 2022-01-02T09:20:41.994Z
Date : 2022-01-02T09:20:47.994Z
Date : 2022-01-02T09:20:53.993Z
Date : 2022-01-02T09:20:59.993Z
Date : 2022-01-02T09:21:11.993Z
Date : 2022-01-02T11:41:29.999Z
Date : 2022-01-02T11:41:35.999Z
Date : 2022-01-02T11:41:41.999Z
Date : 2022-01-02T11:41:47.999Z
Date : 2022-01-02T11:41:53.999Z
Date : 2022-01-02T11:41:59.999Z
Date : 2022-01-02T11:42:11.999Z
Date : 2022-01-02T11:42:17.999Z
Date : 2022-01-02T11:42:23.998Z
Date : 2022-01-02T11:42:29.999Z
Date : 2022-01-02T11:42:35.999Z
Date : 2022-01-02T11:42:41.999Z
Date : 2022-01-02T11:42:47.999Z
Date : 2022-01-02T11:42:53.999Z
Date : 2022-01-02T11:42:59.999Z
Date : 2022-01-02T11:43:23.100Z
Interesting note
The program that create the csv had bug and last date was 2022-01-02T11:43:23.1000
So I added to Power shell code when last character was not a Z to replace with zero. The last row in dates above is the corrected date.
C:\Users\jwarburg\Desktop\StoreCSVSMFLS.ps1 :
ErrorRecord : Exception calling "ExecuteNonQuery" with "0" argument(s): "Conversion failed when
converting date and/or time from character string."
WasThrownFromThrowStatement : False
Message : Exception calling "ExecuteNonQuery" with "0" argument(s): "Conversion failed when
converting date and/or time from character string."
C:\Users\jwarburg\Desktop\StoreCSVSMFLS.ps1 :
ErrorRecord : Exception calling "ExecuteNonQuery" with "0" argument(s): "Conversion failed when
Data : {}
converting date and/or time from character string."
WasThrownFromThrowStatement : False
Message : Exception calling "ExecuteNonQuery" with "0" argument(s): "Conversion failed when
converting date and/or time from character string."
Data : {}
InnerException : System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting date
and/or time from character string.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean
breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
InnerException : System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting date
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand
cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler,
TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior
runBehavior, String resetOptionsString, Boolean isInternal, Boolean
forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior,
RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task,
Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean
describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior,
RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1
completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean
inRetry)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1
completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache,
and/or time from character string.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean
breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand
cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler,
TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior
runBehavior, String resetOptionsString, Boolean isInternal, Boolean
forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior,
RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task,
Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at CallSite.Target(Closure , CallSite , Object )
ClientConnectionId:b62d09f5-a2b0-4076-ac6e-2716779625b0
Error Number:241,State:1,Class:16
TargetSite : Void CheckActionPreference(System.Management.Automation.Language.FunctionContext,
describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior,
System.Exception)
StackTrace : at
System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext
funcContext, Exception exception)
at lambda_method(Closure , Object[] , StrongBox`1[] , InterpretedFrame )
RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1
completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean
inRetry)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1
completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache,
HelpLink :
Source : System.Management.Automation
HResult : -2146233087
At line:1 char:1
+ .\StoreCSVSMFLS.ps1
+ ~~~~~~~~~~~~~~~~~~~
Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
+ CategoryInfo : NotSpecified:
) [Write-Error], WriteErrorException+ FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,StoreCSVSMFLS.ps1
at CallSite.Target(Closure , CallSite , Object )
ClientConnectionId:b62d09f5-a2b0-4076-ac6e-2716779625b0
Error Number:241,State:1,Class:16
TargetSite : Void CheckActionPreference(System.Management.Automation.Language.FunctionContext,
System.Exception)
StackTrace : at
System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext
funcContext, Exception exception)
at lambda_method(Closure , Object[] , StrongBox`1[] , InterpretedFrame )
HelpLink :
Source : System.Management.Automation
HResult : -2146233087
At line:1 char:1
+ .\StoreCSVSMFLS.ps1
+ ~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified:
) [Write-Error], WriteErrorException+ FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,StoreCSVSMFLS.ps1
C:\Users\jwarburg\Desktop\StoreCSVSMFLS.ps1 :
MyCommand :
BoundParameters : {}
UnboundArguments : {}
C:\Users\jwarburg\Desktop\StoreCSVSMFLS.ps1 :
MyCommand :
BoundParameters : {}
UnboundArguments : {}
ScriptLineNumber : 280
ScriptLineNumber : 280
OffsetInLine : 12
HistoryId : -1
ScriptName : C:\Users\jwarburg\Desktop\StoreCSVSMFLS.ps1
Line : $insertCommand.ExecuteNonQuery()
OffsetInLine : 12
HistoryId : -1
ScriptName : C:\Users\jwarburg\Desktop\StoreCSVSMFLS.ps1
Line : $insertCommand.ExecuteNonQuery()
