I have a Z that appears at the end of my date field

Alexis Roche 0

Hello, I'm trying to retrieve all the data that was modified yesterday on my ERP via an API that is accessible in Odata. However, I'm having an issue with my date field, as I get the character Z appearing at the end of the variable.

I tried converting it to a string to concatenate, and also tried removing the rightmost character from my string. I saw a post suggesting to do this: formatDateTime(item().name, 'yyyy-MM-ddTHH:mm:ss:fffff', which works, but I can't keep the milliseconds. My API doesn't accept them.

Chandra Boorla 1,500 Reputation points Microsoft Vendor

2024-09-26T10:24:49.4733333+00:00
Hi @Alexis Roche

Greetings & Welcome to Microsoft Q&A forum! Thanks for posting your query!

As I understand that you are trying to format a date-time string in Azure Data Factory so that it can be used in an OData API request, but the API does not accept milliseconds or the 'Z' character at the end of the string. The 'Z' character indicates that the time is in UTC (Coordinated Universal Time).

In Azure Data Factory, to achieve a date format that excludes the 'Z' and milliseconds, you can utilize the formatDateTime function with an alternative format string.

formatDateTime(item().name, 'yyyy-MM-ddTHH:mm:ss')

This will give you the date and time in the format yyyy-MM-ddTHH:mm:ss, which should be acceptable for your API.

If you need to ensure the time is in your local timezone, you might need to adjust the time accordingly before formatting it.

For more details please refer: formatDateTime

If the issue still persists, please provide more details about your pipeline configuration and API. I would greatly appreciate it if you could provide a screenshot.

I hope this information helps. Please do let us know if you have any further queries.
Alexis Roche 0 Reputation points

2024-09-26T13:59:32.73+00:00
I used this formula :

@formatDateTime(addDays(utcnow(),-1), 'yyyy-MM-ddT00:00:00')

And i still have the Z at the end.
Chandra Boorla 1,500 Reputation points Microsoft Vendor

2024-09-27T13:55:48.25+00:00

Hi @Alexis Roche

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.

Thank you.

1 answer

Chandra Boorla 1,500 Reputation points Microsoft Vendor

2024-09-26T15:14:12.44+00:00
Hi @Alexis Roche

Apologies for the inconvenience.

As I mentioned earlier, the "Z" at the end of the formatted date-time string in your Azure Data Factory expression indicates that the time is in UTC (Coordinated Universal Time). This is a standard format used to represent time zones in ISO 8601.

If you want to remove the Z from the end of the formatted date string, you can use the substring function to exclude the last character.

Calculate the formatted date string without the Z:

@substring(formatDateTime(addDays(utcnow(), -1), 'yyyy-MM-ddT00:00:00'), 0, 19)

Explanation:

formatDateTime(addDays(utcnow(), -1), 'yyyy-MM-ddT00:00:00'): This part generates the date string with the Z at the end.

substring(<date-string>, 0, 19): This part extracts the first 19 characters of the date string, effectively removing the Z.

This will format the date-time string in the specified format, but without the "Z" at the end.

I hope this information helps.

Thank you.
Please sign in to rate this answer.
Chandra Boorla 1,500 Reputation points Microsoft Vendor

2024-09-28T15:03:44.96+00:00

Hi @Alexis Roche

Following up to see if the above answer was helpful. 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.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

I have a Z that appears at the end of my date field

1 answer

Your answer