SQL Server : trying to convert a date with a case

Question

I have this SQL code that does warranty registration data transfer to the equipment sheet. But i get 4 error messages that are

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.

Msg 102, Level 15, State 1, Line 16
Incorrect syntax near 'T1'.

This is the code that I am using that transforms the date and puts it in the correct format like YYYY-MM-DD

UPDATE TOP (100) [TEST].[dbo].[Report]
SET [U_DateA] = SUBSTRING((T1.[DateAchat], 3, 1) = '-' 

    [U_CoName] = LEFT((T1.[Individu_prenom] + ' ' + T1.[Individu_nom]), 50),
    [U_CoRue] = T1.[Individu_adresse],
    [U_CoVil] = T1.[Individu_ville],

FROM 
    (SELECT 
         idForm, [FormDateEntree], REPLACE([DateAchat], '/', '-') AS DateAchat,
         [Individu_prenom], [Individu_nom], [Individu_adresse],
         [Individu_ville], [Individu_CpZip], [Individu_Telephone],
         [Individu_Email], [Individu_pays], [Individu_provEtat], 
         [ModeleNo],[ModeleSerie] 
     FROM
         [dTEST2].[dbo].[RegistrTest]) T1

Does anyone have an idea?

Answer 1

First, you have two (( in the first substring, you need only one. CASE WHEN SUBSTRING(T1.[DateAchat], 3, 1) = '-'

Second, remove AS DateAchat from. You don't have to specify a column name in a update query!

Instead of manipulating date and string (which is dependant of your system), do this to have a date format to yyyy-mm-dd :

... SET [U_DateA] = CONVERT(CHAR(10), T1.[DateAchat], 126) ...

I hope [U_DateA] is a string. If this a date field, why not keep it as a date?