Elroy Taulton
Elroy Taulton

Reputation: 5

How can I change the format of a field from STRING to DATETIME2 in a SET statement

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

Answers (0)

Related Questions