convert date string to Date in dataverse

(4) Share

Report

Posted on by HH-22102055-0

Hi Guys,

I have a text-based date column. Do you have any idea to convert to Date type or DateTime type column in Dataverse?

Thanks

Hannah

Categories:

Dynamics 365 general

I have the same question (0)

All responses (6)

Answers (1)

HH-22102055-0 89 on at

Like (1)

Report

convert date string to Date in dataverse

@Daivat Vartak (v-9davar)

I tried the expression:

parseDateTime(concat(triggerOutputs()?['body/retentiondate'], 'T23:59:59.000'), 'yyyy-MM-ddTHH:mm:ss.fff', 'America/New_York')

The flow gives error: Unable to process template language expressions in action 'Compose' inputs at line '0' and column '0': 'The template language function 'parseDateTime' was unable to find the locale associated with 'yyyy-MM-ddTHH:mm:ss.fff'. The provided locale name is either invalid or does not have a locale associated with it. Please see https://aka.ms/logicexpressions#parsedatetime for usage details.'.

And I checked https://aka.ms/logicexpressions#parsedatetime again. The function is defined as: parseDateTime('<timestamp>', '<locale>'?, '<format>'?) instead of parseDateTime('<timestamp>', '<format>'?, '<timezone>'?).

I am wondering if I should use convertToUtc('<timestamp>', '<sourceTimeZone>', '<format>'?) in another 'Compose' action.

thank you,

Hannah

Was this reply helpful? Yes No
Suggested answer

Daivat Vartak (v-9d... 7,829 Super User 2025 Season 2 on at

Like (1)

Report
convert date string to Date in dataverse

Hello HH-22102055-0,

You're encountering a common timezone issue with date/time conversions in Power Automate. Even though you're setting the time to 11:59 PM, the flow is interpreting and storing it in UTC, and then Dataverse is displaying it based on your user's timezone (UTC-05:00), resulting in the 6-hour difference.

Here's how to fix it within your Power Automate flow:

1. Specify Timezone in parseDateTime (Crucial):

The parseDateTime function can take a timezone parameter, but you're currently using en-US for the locale, not the timezone. You must explicitly tell the function that your input date/time string is already in a specific timezone before converting it to UTC.

Since your date string doesn't include timezone information, Power Automate assumes it's in the current timezone of the flow's execution environment (which is often UTC).

2. Set the Timezone to UTC-05:00 for the parseDateTime function:

'America/New_York': Replace this with the appropriate IANA timezone name that corresponds to UTC-05:00. Using America/New_York is an example. You should find the IANA name for your specific region that uses UTC-05:00.

3. Convert to UTC (If Necessary):

If your Dataverse DateTime field is stored in UTC (which is recommended), you might need to explicitly convert the parsed date/time to UTC after parsing it in the compose action. However, the above step should handle the conversion.

4. Update Record:

When you update the Dataverse record, the DateTime value you're sending should now be correctly interpreted as 11:59 PM in your specified timezone. Dataverse will store it in UTC, but display it correctly in your user's timezone.

Complete Example (Compose Action Expression):

parseDateTime(concat(triggerOutputs()?['body/retentiondate'], 'T23:59:59.000'), 'yyyy-MM-ddTHH:mm:ss.fff', 'America/New_York')

Explanation:

By including the timezone parameter ('America/New_York') in the parseDateTime function, you're telling Power Automate that the input date and time are in that timezone.

Power Automate will then correctly convert this to UTC before storing it in Dataverse.

When Dataverse displays the date/time, it will convert it back to your user's timezone (UTC-05:00), showing the correct 11:59 PM time.

If my answer was helpful, please click Like, and if it solved your problem, please mark it as verified to help other community members find more. If you have further questions, please feel free to contact me.

My response was crafted with AI assistance and tailored to provide detailed and actionable guidance for your Microsoft Dynamics 365 query.

Best Regards,

Daivat Vartak

Was this reply helpful? Yes No
HH-22102055-0 89 on at

Like (1)

Report

convert date string to Date in dataverse

@Daivat Vartak (v-9davar) Thank you very much for your detailed solution options. I used the 2nd option as I think it's simple conversion. in 'Compose' action, the expression is

parseDateTime(

concat(triggerOutputs()?['body/retentiondate'], 'T23:59:59.000'), 'en-US', 'yyyy-MM-ddTHH:mm:ss.fff') triggerOutputs()?['body/retentiondate'] is dynamic content. The year may be different, but month/date is always 12-31. Take '2036-12-31' for example, after running the flow, the converted datetime column shows '12/31/2036 6:59 PM' in dataverse. but I want it to display exactly as '12/31/2036 11:59 PM'. My timezone is UTC-05:00. Do you have any advise to fix the time difference issue in this cloud flow?

Was this reply helpful? Yes No
Verified answer

Daivat Vartak (v-9d... 7,829 Super User 2025 Season 2 on at

Like (4)

Report
convert date string to Date in dataverse

Hello HH-22102055-0,

You have a text-based date column in Dataverse and want to convert it to a Date or DateTime column. This is a common data migration or integration challenge. Here's a breakdown of how to approach this, along with important considerations:

1. Dataverse Web API (For larger datasets or programmatic updates):

If you're dealing with a large number of records or need to automate this conversion, the Dataverse Web API is the most efficient method.

Fetch Data: Use the Web API to retrieve the records with the text-based date column.

Parse Dates: In your code (e.g., JavaScript, C#), parse the text date values into Date objects. You'll need to know the exact format of the text dates (e.g., YYYY-MM-DD, MM/DD/YYYY, DD-MM-YYYY). Use appropriate date parsing functions in your chosen language.

Update Records: Use the Web API to update the records, setting the new Date or DateTime column with the parsed Date objects

2. Power Automate (For smaller datasets or simpler conversions):

For smaller datasets or if you prefer a no-code/low-code approach, Power Automate can be a good option.

Trigger: Create a flow that triggers on a schedule or manually.

List Records: Use the "List Records" action to retrieve the records with the text-based date.

Apply to Each: Use an "Apply to each" loop to process each record.

Compose Action (For Date Parsing): Use a "Compose" action with an expression to parse the date. Power Automate has date and time functions you can use. You might need to use string manipulation functions to extract the year, month, and day parts if your date format is not directly supported.

Update a Record: Use the "Update a record" action to update the record with the parsed Date or DateTime value.

3. Excel Import/Export (For smaller datasets):

If you have a very small number of records, you could use Excel:

Export to Excel: Export the data to Excel.

Parse Dates in Excel: Use Excel formulas to parse the text date values into proper Excel dates.

Import to Dataverse: Import the updated Excel file back into Dataverse.

Important Considerations:

Date Format: The most critical aspect is knowing the exact format of your text-based dates. If the format is inconsistent, you'll need more complex parsing logic.

Data Validation: Before converting, validate your data to identify any invalid or unusual date values. This will prevent errors during the conversion process.

Time Component (DateTime): If you're converting to a DateTime column, decide how you want to handle the time component. You might use a default time (e.g., 00:00:00) or extract the time from the text if it's available.

Testing: Test your conversion process thoroughly in a non-production environment before applying it to your production data.

Rollback Plan: Have a rollback plan in case something goes wrong during the conversion. Backing up your data is always a good idea.

Which Method to Choose:

Web API: Best for large datasets, automation, and complex conversions.

Power Automate: Good for smaller datasets, simpler conversions, and no-code/low-code approach.

Excel: Only for very small datasets where manual manipulation is feasible.

If my answer was helpful, please click Like, and if it solved your problem, please mark it as verified to help other community members find more. If you have further questions, please feel free to contact me.

My response was crafted with AI assistance and tailored to provide detailed and actionable guidance for your Microsoft Dynamics 365 query.

Best Regards,

Daivat Vartak

Was this reply helpful? Yes No
HH-22102055-0 89 on at

Like (1)

Report

convert date string to Date in dataverse

@Adz Sorry my mistake. I did not give a clear description of my issue. I have a column (Year, this can be whole number or single line of text). In order to display the value of year without comma, I chose single line of text as data type. Then comes another column (Retention Date, formula column), which has to be using this formula Concatenate(Text(Value(Year) + 11, "####"), "-12-31"). The client want the Retention Date always show (year +11)-12-31 as value. Finally comes the last column (Retain, a yes/no column) to be used as filter to display expired date. The Retain column uses the condition 'Retention Date > Today()' to automatically update its value. But now retention date is not DateTime datatype, there is no formula like DateValue or DateTimeValue to convert the text-based date(Retention Date) to DateTime. That's why I am asking for help. Hope it's much more clear now.

Was this reply helpful? Yes No
Adam_Travers 329 on at

Like (2)

Report

convert date string to Date in dataverse

Hi Hannah,

Unfortunately you cannot do this so you will need to delete and recreate a new column. The issue you may have here though is you may have dependencies on the existing column and you may need to copy the existing data from the old column to the new one but with different data types this maybe a little tricky but I can help further with that if needed.

You may find this post helpful for clarification on the above.

https://www.bing.com/videos/riverview/relatedvideo?q=power+apps+dataverse+change+column+type&mid=2A0E8FD15BFCBF365B152A0E8FD15BFCBF365B15&FORM=VIRE

Was this reply helpful? Yes No