Reputation: 5
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
Upvotes: 0
Views: 33