How can I update table column values from YYYYMMDD to YYYYMM-CodePudding

Account_Open_Date
19931122
20041102

So I want to convert the dates to be

Account_Open_Date
199311
200411

SELECT 
    CAST(CONVERT(varchar(6), a.Account_Open_Date, 112) AS int)
FROM 
    [tempdb].[dbo].[SQL Test Data] a
WHERE 
    Account_Open_Date is NOT NULL;

This select statement pulls up the dates how I want them but I want to update the table to the same format and am having a hard time with Updating. I don't have a good example of what I have tried.

UPDATE x.Account_Open_Date
SET a.Account_Open_Date = CAST(CONVERT(varchar(6), Account_Open_Date, 112) AS int)
FROM 
    ([tempdb].[dbo].[SQL Test Data]) x
WHERE 
    Account_Open_Date is NOT NULL;

CodePudding user response：

The comments bring up excellent points. Seeing as you are doing this on a temp table I will assume you are testing and wont dive into the points brought up.

Fixed your update statement. You were close.

update x 
SET 
    x.Account_Open_Date = CAST(convert(varchar(6),Account_Open_Date,112) as int) 
FROM 
    [tempdb].[dbo].[SQL Test Data] x 
WHERE 
   Account_Open_Date is NOT NULL;