0

I am running the script below but am receiving the following error message:

Conversion failed when converting date and/or time from character string.

I assume it is associated with [CREATION_DATE]=t.CREATEDATE statement. The CREATION_DATE field is a DATETIME2 field and t.CREATEDATE appears to be a string. Can I add CONVERT or FORMAT to get the CREATEDATE field into the appropriate format for setting it?

WITH DOCUMENT_ID AS
  (SELECT 
    DWDOCID, 
    VALUE, 
    ROW_NUMBER() OVER(partition by DWDOCID Order by DWDOCID) AS RowNum
  FROM [SUVA]
    CROSS APPLY STRING_SPLIT(DWNAME,'^')
  WHERE DWDOCID = '1721' AND DOCUMENT_TYPE IS NULL
    )
UPDATE SUVA
  SET
    [STUDENT_NUMBER]=t.STUDENTNUMBER,
    [STUDENT_ID]=t.STUDENTID,
    [LAST_NAME]=t.LASTNAME,
    [FIRST_NAME]=t.FIRSTNAME,
    [CREATION_DATE]=t.CREATEDATE, 
    [DOCUMENT_TYPE]=t.DOCTYPE
FROM SUAM s INNER JOIN
    (
        SELECT DWDOCID,
            [1] AS DRAWER,
            [2] AS DOCID,
            [3] AS STUDENTNUMBER,
            [4] AS STUDENTID,
            [5] AS LASTNAME,
            [6] AS FIRSTNAME,
            [7] AS FIELD5,
            [8] AS DOCTYPE,
            [9] AS CREATEDATE,
            [10] AS DOCUMENTYEAR
        FROM DOCUMENT_ID
            PIVOT
            (MAX(VALUE)
        FOR RowNum in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])) AS PVT
    )t 
ON s.dwdocid=t.dwdocid
Abra
  • 19,142
  • 7
  • 29
  • 41
Elroy Taulton
  • 5
  • 3
  • what is the schema of table SUVA? – Luis LL Feb 23 '23 at 08:21
  • Have you tried [TRY_CONVERT](https://learn.microsoft.com/en-us/sql/t-sql/functions/try-convert-transact-sql) yet? Check the _CAST and CONVERT_ link on that page to see what date formats the _style_ numbers map to. – AlwaysLearning Feb 23 '23 at 09:43
  • There are a lot of fields already set but for the ones in question, they are ``` STUDENT_NUMBER NVARCHAR(255) STUDENT_ID NVARCHAR(255) LAST_NAME NVARCHAR(255) FIRST_NAME NVARCHAR(255) CREATION_DATE DATETIME2(7) DOCUMENT_TYPE NVARCHAR(255) ``` – Elroy Taulton Feb 23 '23 at 10:35
  • I tried [CREATION_DATE]=TRY_CONVERT(DATETIME2,'t.CREATEDATE'), The result was that it didn't perform an action on JUST DWDOCID = '1721', but it updated all the rows , column of that date field to NULL – Elroy Taulton Feb 23 '23 at 10:45

0 Answers0