Incorrect Syntax

Question

Hi

On below code i am getting error - The conversion of a varchar data type to a datetine data resulted in out-of-range value.

(SELECT PaymentDate = STUFF((
         SELECT ',' + A0.docdate from Orct A0 inner join Rct2 A1 on A1.Docnum = A0.docentry and A1.baseabs = T0.docentry
            FOR XML PATH('')
         ), 1, 1, ''))

Thanks

Jayraj Chhaya · Answer

Hi Ramco Ramco, Problem The problem lies in the conversion of the docdate column from the Orct table to a datetime data type. Cause The cause of the error is that the docdate column contains a value that cannot be converted to a valid datetime format. This could be due to various reasons, such as incorrect data entry or a mismatch between the expected datetime format and the actual value. Solution To fix the error, we need to ensure that the values in the docdate column are in a valid datetime format. Here are a few steps you can take to resolve the issue: Check the data in the docdate column of the Orct table. Look for any values that are not in the expected datetime format. Correct any incorrect or invalid datetime values in the docdate column. You can either update the values directly or use a conversion function to transform them into the correct format. If there are any datetime values that cannot be corrected, consider excluding them from the result set or handling them separately to avoid the conversion error. Here's an example of how you can use the TRY_CONVERT function to handle the conversion and exclude any invalid datetime values: (SELECT PaymentDate = STUFF(( SELECT ',' + TRY_CONVERT(datetime, A0.docdate) from Orct A0 inner join Rct2 A1 on A1.Docnum = A0.docentry and A1.baseabs = T0.docentry FOR XML PATH('') ), 1, 1, '')) By using the TRY_CONVERT function, any values in the docdate column that cannot be converted to a valid datetime format will be returned as NULL . This will prevent the conversion error and allow the query to execute successfully.

Tahir Ansari · Answer

Hi Ramco, SELECT PaymentDate = STUFF(( SELECT ',' + CONVERT(varchar, A0.docdate, 120) FROM Orct A0 INNER JOIN Rct2 A1 ON A1.Docnum = A0.docentry AND A1.baseabs = T0.docentry FOR XML PATH('') ), 1, 1, '')

Vishal Yelve · Answer

Hi Ramco, try this (SELECT PaymentDate = STUFF(( SELECT ',' + convert(datetime, A0.docdate) from Orct A0 inner join Rct2 A1 on A1.Docnum = A0.docentry and A1.baseabs = T0.docentry FOR XML PATH('') ), 1, 1, '')) OR (SELECT PaymentDate = STUFF(( SELECT ',' + CONVERT(VARCHAR, A0.docdate, 101) from Orct A0 inner join Rct2 A1 on A1.Docnum = A0.docentry and A1.baseabs = T0.docentry FOR XML PATH('') ), 1, 1, ''))

Subarta Ray · Answer

SELECT PaymentDate = STUFF(( SELECT ',' + CONVERT(varchar, A0.docdate, 120) FROM Orct A0 INNER JOIN Rct2 A1 ON A1.Docnum = A0.docentry AND A1.baseabs = T0.docentry WHERE ISDATE(A0.docdate) = 1 -- Ensure it's a valid date FOR XML PATH('') ), 1, 1, '')