question

ewinkiser avatar image
0 Votes"
ewinkiser asked ewinkiser commented

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

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

81782-image.png



azure-data-factorysql-server-transact-sql
image.png (49.8 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Maybe add a new column like this:

 alter table MercerReportTableMod add StartDate2 as FORMAT(CONVERT(datetime, StartDate), 'yyyyMMdd')

Then use StartDate2 instead of StartDate when you need the special format. The column is calculated automatically and you do not have to update it.

You can also define a view.



0 Votes 0 ·
NandanHegde-7720 avatar image
0 Votes"
NandanHegde-7720 answered ewinkiser commented

Hey @ewinkiser ,
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

· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@NandanHegde-7720 Thanks for your and everyone's answer.

When I try
SELECT *, FORMAT(CONVERT(date, StartDate), 'yyyyMMdd') AS StartDate FROM [MercerReportTableMod]

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

When I do the SELECT FORMAT I get the correct formatting as follows which looks good.
![81952-image.png][1]

However when I do the SELECT * shown above I get the following with no change in the date format
![81898-image.png][2]

I would like to have when I do a SELECT the date in the format 20071009; is this possible or am I wasting my time trying this? I want to use the SELECT in my Copy Source Query if I can get the date format correct.

Thanks so much!
Mike
[1]: /answers/storage/temp/81952-image.png
[2]: /answers/storage/temp/81898-image.png

0 Votes 0 ·
image.png (23.7 KiB)
image.png (101.4 KiB)

Hi @ewinkiser,

you can have the following query in the CopyActivity, which will add another column at the end of your existing columns. Then in the CopyActivity --> Mapping, select FormattedStartDate instead of StartDate. Hope this will help! Thanks!

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

1 Vote 1 ·

@nasreen-akter .....that worked!!!!See below! I was getting sick at my stomach wondering if I was going to have to work on this all weekend....now I can fix other bugs.....I can not thank you enough!! and I also thank everyone else's awesome input on this thread!!

I am happy!! Thanks so much!!
Mike Kiser


81925-screenshot-2021-03-26-133159.jpg


1 Vote 1 ·
Show more comments
TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered ErlandSommarskog commented

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

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @TomCooper-6989

Thanks for the awesome explanation; now I understand what is going on. I need to store the formatted date as '20171231' so I think I will try changing the datatype to nvarchar(50) and then try the UPDATE in a Stored Proc...does that make sense? I have to do some remapping so I am wanting to run this by you first. My goal is to bring back the table with the '20171231' formatted field in a COPY Activity Source.

Tom, I also understand what you describe to display it, however, this is an Integration Pipeline feed for Ceridian HR and they demand that the date is sent to them in the '20171231' format. This is killing me lol I bet I have spent 2 days on this now and am running behind.

Thank you!
Mike Kiser

0 Votes 0 ·

I need to store the formatted date as '20171231' so I think I will try changing the datatype to nvarchar(50) and then try the UPDATE in a Stored Proc...does that make sense?

No! Never store date/time values as strings or numbers. It will only cause you grief and misery. It will not take long until you have something like '20171332' in that column. Or Kilroy was here.

Always store date and time with the proper data types.


1 Vote 1 ·

First, I am not a user of Copy Activity. So this may or may not make sense. But I'm guessing your source is something like
"SQLReaderQuery": "Select <list of columns> From MercerReportTableMod"
Somewhere in that list of columns you have StartDate, change that to

Format(Convert(datetime, StartDate), yyyyMMdd) As StartDate

Alternatively, create a view which selects all the columns in MercerReportTableMod except use the above formula for the StartDate column. Then use that view as your SQLReaderSource.

If it were my system, I would prefer doing that to changing the datatype in the current table.

But remember, I don't know or use Copy Activity, so all of the above might be gibberish for all I know.

Tom

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @ewinkiser

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.