Trying to Update a Column in a Table using FORMAT before my COPY Activity - URGENT please

Question

Hi @Nasreen Akter @MartinJaffer-MSFT

Ok, the following will give me the results from a SELECT (see attached)

SELECT FORMAT(CONVERT(datetime, StartDate), 'yyyyMMdd') AS StartDate FROM [MercerReportTableMod]

BUT I NEED TO ACTUALLY UPDATE this field (column) in the Table to have the same results

I have tried the following but it does nothing

UPDATE [MercerReportTableMod]
SET StartDate = FORMAT(CONVERT(datetime, StartDate), 'yyyyMMdd')

PLEASE, how can I actually update the data in the column (the SELECT above works perfectly but of course doesn't update the data)...

Thanks
Mike Kiser

Answer 1

Hey @Mike Kiser ,
Can you please clarify the requirement?
You are saying that you have a copy activity with source and destination as SQLs.
And in source your query is : SELECT FORMAT(CONVERT(datetime, StartDate), 'yyyyMMdd') AS StartDate FROM [MercerReportTableMod]
and in destination, the column populated is showing different date format?

This might help us clear the ADF copy activity configs.

On a side note:
you can have the update query executed in a lookup activity in ADF after the copy activity ,
But this can be avoided by properly managing the copy activity

Answer 2

So the question is "what datatype is the StartDate column?" If it is some form of a datetime (e.g., date, datetime, datetime2, smalldatetime), then what is stored in that column is the value, but it is just the value, NOT any formatting. So, for example, if you put January 1, 2020 into a datetime column, then that datetime column will contain a value that means January 1, 2020 00:00:00.000. But that is all.

So then when you select that column and display it, it is formatted into a character string either by some function (like FORMAT) or if you don't specify how to convert it it will be converted to the default format used by whatever the front end you are using to do the Select (e.g. SSMS).

So if the datatype of StartDate is date, then it will have a value which represents a particular date for example Dec 31, 2017, but has no format. When you do

UPDATE [MercerReportTableMod]
SET StartDate = FORMAT(CONVERT(datetime, StartDate), 'yyyyMMdd')

SQL converts StartDate to a datetime, then the FORMAT function converts it to the character string '20171231'. Then you store that value back into StartDate. But since StartDate is a date column, it cannot store the format, it only stores the value and the format is now gone.

If you need to store a formatted date, datetime, etc, you would have to store it in a character datatype, for example char, varchar, nvarchar, etc. So if you must store a formatted value, you would have to change the datatype. BUT, almost certainly you do not want to do that. By far the best practice is to store the data in the correct datatype, and format it as needed when you need to display it.

Tom

Answer 3

Hi @Mike Kiser ，

I did a test with my data, and your idea is correct:

    CREATE TABLE #testa(CustID INT,CustCitytime DATE)         
    INSERT INTO #testa VALUES(1,null),(2,null)  
                             ,(3,null)    
       
    CREATE TABLE #testb(CustNumber INT,CustCitytime DATE)         
    INSERT INTO #testb VALUES(1,'2021-12-23'),(2,'2021-1-23')  
                             ,(3,'2021-2-23')               
                  
    UPDATE a  
    SET a.CustCitytime=FORMAT(CONVERT(datetime, b.CustCitytime), 'yyyyMMdd')  
    FROM #testa a   
    JOIN #testb b ON a.CustID=b.CustNumber;  
      
    SELECT * FROM #testa   

Please refer to the above code, if your problem is not resolved, please provide complete data.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.