Incorrect Syntax

Hi Ramco Ramco,

The problem lies in the conversion of the docdate column from the Orct table to a datetime data type.

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:

1. Check the data in the docdate column of the Orct table. Look for any values that are not in the expected datetime format.
2. 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.
3. 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.

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, '')
 

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, '')

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, ''))