How to Convert a datatype of a date column from UTC to PST in COPY activity of ADF?

Question

How to Convert a datatype of a date column from UTC to PST in COPY activity of ADF?

Prakatheeswari 20

I am using a copy activity to fetch data from api and put it in sql db. I have a date column in my dataset and it is in UTC format. When I put it into my DB I want it to be in PST. In the source side I added additional column and gave the following as my value.

@formatDateTime(convertTimeZone(utcNow(), 'UTC', 'Pacific Standard Time'), 'yyyy-MM-dd HH:mm:ss.fffffff')

But this didn't change my datatype. I want to to change the format of the column from utc to pst in the pipeline.

phemanth 15,765 Reputation points Microsoft External Staff Moderator

2024-02-17T05:28:23.7366667+00:00
@Prakatheeswari
Thanks for posting your question in the Microsoft Q&A forum.

It seems like you’re trying to convert the timezone of a date column from UTC to PST in your Azure Data Factory pipeline. The expression you’re using is correct for converting the current time from UTC to PST, but it won’t work on a column in your dataset because utcNow() only gets the current timestamp.

Instead, you should use the column name from your dataset in place of utcNow(). Assuming your date column is named dateColumn, the expression would look something like this:

@formatDateTime(convertTimeZone(item().dateColumn, 'UTC', 'Pacific Standard Time'), 'yyyy-MM-dd HH:mm:ss.fffffff')

This will take the date from dateColumn, convert it from UTC to PST, and then format it as a string in the format you specified. Please replace dateColumn with your actual column name. If you want the result to be a datetime object instead of a string, you can remove the formatDateTime() function:

@convertTimeZone(item().dateColumn

refer:https://stackoverflow.com/questions/67084644/how-can-we-convert-timezone-in-azure-data-factory-to-specified-timezones Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Prakatheeswari 20 Reputation points

2024-02-17T06:02:29.2133333+00:00

Thank you for your answer Phemanth, But I am getting the following error

@item() syntax can only be used in the filter activity or child activities of a foreach activity

In my pipeline I just have only one copy activity, in the source I am fetching data from the API and in the sink i am putting it in SQL DB. I was trying to add a new column in the source side and convert the utc time to pst and put in that new column, can you please tell how else can i do this.
phemanth 15,765 Reputation points Microsoft External Staff Moderator

2024-02-17T06:20:15.09+00:00
@Prakatheeswari
I see, you’re correct that the @item() syntax is only available in certain contexts. If you’re using a single copy activity and want to transform the data as it’s being copied, you might need to use a data flow activity instead. In a data flow activity, you can use derived column transformations to add a new column with the converted time. Here’s a rough example of how you might set this up: Create a new data flow. Add your source dataset.

Add a derived column transformation. In the settings for the derived column transformation, you can add a new column and use an expression to convert the time. The expression might look something like this:

toTimestamp(toLocalDateTime(makeTimestamp(dateColumn), 'PST'), 'yyyy-MM-dd HH:mm:ss.SSS')

This expression takes a timestamp (dateColumn), converts it to a local datetime in PST, and then formats it as a string. Replace dateColumn with the name of your date column. Add your sink dataset and map the columns appropriately.

This should give you a new column in your SQL database with the date and time converted to PST. Please note that you’ll need to adjust this based on your specific pipeline and data
Prakatheeswari 20 Reputation points

2024-02-17T18:35:29.8366667+00:00

I am fetching my data from Shopify API and In data flow activity I cannot fetch the data from shopify api in the source side, the option is disabled.
Prakatheeswari 20 Reputation points

2024-02-17T18:53:15.95+00:00

Thank you Phemanth. I am getting my source data from shopify api and In data flow activity, I don't have that option for shopify api in the source side.
phemanth 15,765 Reputation points Microsoft External Staff Moderator

2024-02-19T09:49:05.7666667+00:00
@Prakatheeswari consider the following workaround:

Ingest the data as-is: In your Copy activity, ingest the data from the Shopify API into your SQL database without any transformations.

Transform in SQL: After the data is in your SQL database, you can then perform the timezone conversion using SQL commands. This could be done through a Stored Procedure in SQL Server that gets triggered after the data ingestion.

Here’s an example of how you might do this in SQL Server:

UPDATE YourTable SET YourDateColumn = DATEADD(HOUR, -8, YourDateColumn)

This command updates YourDateColumn in YourTable by subtracting 8 hours from each date and time (converting from UTC to PST). Please replace YourTable and YourDateColumn with your actual table and column names. Please note that daylight saving time is not considered in this example. If you need to account for daylight saving time, the SQL command will be more complex as you’ll need to determine when to subtract 7 hours instead of 8.
phemanth 15,765 Reputation points Microsoft External Staff Moderator

2024-02-20T09:50:13.3133333+00:00

@Prakatheeswari We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
phemanth 15,765 Reputation points Microsoft External Staff Moderator

2024-02-21T07:53:29.3966667+00:00

@Prakatheeswari just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Your answer

phemanth 15,765 Reputation points Microsoft External Staff Moderator

2024-02-17T05:28:23.7366667+00:00

@Prakatheeswari
Thanks for posting your question in the Microsoft Q&A forum.

It seems like you’re trying to convert the timezone of a date column from UTC to PST in your Azure Data Factory pipeline. The expression you’re using is correct for converting the current time from UTC to PST, but it won’t work on a column in your dataset because utcNow() only gets the current timestamp.

Instead, you should use the column name from your dataset in place of utcNow(). Assuming your date column is named dateColumn, the expression would look something like this:

@formatDateTime(convertTimeZone(item().dateColumn, 'UTC', 'Pacific Standard Time'), 'yyyy-MM-dd HH:mm:ss.fffffff')

This will take the date from dateColumn, convert it from UTC to PST, and then format it as a string in the format you specified. Please replace dateColumn with your actual column name. If you want the result to be a datetime object instead of a string, you can remove the formatDateTime() function:

@convertTimeZone(item().dateColumn

refer:https://stackoverflow.com/questions/67084644/how-can-we-convert-timezone-in-azure-data-factory-to-specified-timezones Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Prakatheeswari 20 Reputation points

2024-02-17T06:02:29.2133333+00:00

Thank you for your answer Phemanth, But I am getting the following error

@item() syntax can only be used in the filter activity or child activities of a foreach activity

In my pipeline I just have only one copy activity, in the source I am fetching data from the API and in the sink i am putting it in SQL DB. I was trying to add a new column in the source side and convert the utc time to pst and put in that new column, can you please tell how else can i do this.
phemanth 15,765 Reputation points Microsoft External Staff Moderator

2024-02-17T06:20:15.09+00:00

@Prakatheeswari
I see, you’re correct that the @item() syntax is only available in certain contexts. If you’re using a single copy activity and want to transform the data as it’s being copied, you might need to use a data flow activity instead. In a data flow activity, you can use derived column transformations to add a new column with the converted time. Here’s a rough example of how you might set this up: Create a new data flow. Add your source dataset.

Add a derived column transformation. In the settings for the derived column transformation, you can add a new column and use an expression to convert the time. The expression might look something like this:

toTimestamp(toLocalDateTime(makeTimestamp(dateColumn), 'PST'), 'yyyy-MM-dd HH:mm:ss.SSS')

This expression takes a timestamp (dateColumn), converts it to a local datetime in PST, and then formats it as a string. Replace dateColumn with the name of your date column. Add your sink dataset and map the columns appropriately.

This should give you a new column in your SQL database with the date and time converted to PST. Please note that you’ll need to adjust this based on your specific pipeline and data
Prakatheeswari 20 Reputation points

2024-02-17T18:35:29.8366667+00:00

I am fetching my data from Shopify API and In data flow activity I cannot fetch the data from shopify api in the source side, the option is disabled.
Prakatheeswari 20 Reputation points

2024-02-17T18:53:15.95+00:00

Thank you Phemanth. I am getting my source data from shopify api and In data flow activity, I don't have that option for shopify api in the source side.
phemanth 15,765 Reputation points Microsoft External Staff Moderator

2024-02-19T09:49:05.7666667+00:00

@Prakatheeswari consider the following workaround:

Ingest the data as-is: In your Copy activity, ingest the data from the Shopify API into your SQL database without any transformations.

Transform in SQL: After the data is in your SQL database, you can then perform the timezone conversion using SQL commands. This could be done through a Stored Procedure in SQL Server that gets triggered after the data ingestion.

Here’s an example of how you might do this in SQL Server:

UPDATE YourTable SET YourDateColumn = DATEADD(HOUR, -8, YourDateColumn)

This command updates YourDateColumn in YourTable by subtracting 8 hours from each date and time (converting from UTC to PST). Please replace YourTable and YourDateColumn with your actual table and column names. Please note that daylight saving time is not considered in this example. If you need to account for daylight saving time, the SQL command will be more complex as you’ll need to determine when to subtract 7 hours instead of 8.
phemanth 15,765 Reputation points Microsoft External Staff Moderator

2024-02-20T09:50:13.3133333+00:00

@Prakatheeswari We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
phemanth 15,765 Reputation points Microsoft External Staff Moderator

2024-02-21T07:53:29.3966667+00:00

@Prakatheeswari just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

How to Convert a datatype of a date column from UTC to PST in COPY activity of ADF?

Your answer