Windows 10 Convert DateTime is failing with following in Power Shell using SQLSERVER

[jdweng]

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()

 

[ChatGPT]

The SQL error message you provided seems to suggest a conversion failure when trying to convert date or time from character string during an ExecuteNonQuery operation. Since the error seems to occur during the conversion of the date and time values from the CSV file, let's address this issue:

Possible Solutions:​

1. Ensure Correct Date Format:
   • Confirm that the date format from the CSV file matches the expected format for SQL conversion.
   • Ensure that the date and time values extracted from the CSV file are in a format that SQL Server can recognize.
   []Check CSV Date Formatting:
   • Validate that the date and time values in the CSV file are correctly formatted and contain no unexpected characters.
   • Check for any anomalies, unusual characters, or incorrect entries in the date and time fields.
   []Handling Date Conversion:
   • Modify the PowerShell script to preprocess and validate the date values before invoking the SQL ExecuteNonQuery operation.
   • Use PowerShell functions or scripts to clean and format date strings appropriately before SQL operations.
   []Error Handling:
   • Implement error handling in the PowerShell script to capture conversion failures and provide detailed diagnostic logs.
   • Include logging statements to identify problematic date/time values causing the conversion failure.
   []Testing and Debugging:
   • Test the CSV files with a limited dataset to isolate the entries causing the conversion failure.
   • Use debugging techniques to step through the PowerShell script and identify the specific point of failure during the ExecuteNonQuery operation.
2. Consult SQL Documentation:
   • Refer to SQL Server documentation for supported date and time formats that can be directly converted by the DATETIME data type.
   • Ensure compatibility between the CSV date format and the expected format by SQL Server. By carefully examining the date and time values in the CSV file, addressing conversion issues, and enhancing error handling in your PowerShell script, you can troubleshoot and resolve the SQL conversion errors encountered during the ExecuteNonQuery operation.