Answered

Issue with converting Text Date in dd/mm/yyyy format to yyyy-mm-dd

(0) Share

Report

Posted on by ozzie14

Hi PA Community members,

I'm facing an issue with a text to date conversion. I believe it is possibly very common problem, but I have not found a correct solution from any previous posts yet!

In my app, I have customer requests coming in with a date as text, in dd/mm/yyyy format.

I am storing the data in a Collection with the following code:

due_date: Text(DateValue(TextInput_RepDt1.Text), "yyyy-mm-dd")

It is working correctly, unless the day value is more than 12. it then changes the entire date. The screenshot below will help to explain the situation:

The left image is how the data is displayed, and the right image is how the collection stores the date.

This is causing huge problem as I'm storing the above date in a SQL DB table (with a Patch function).

Can anyone please point out what changes I need to make in my formula to convert a date correctly?

Thanks

Ozzie

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Sort by

Michael E. Gernaey 53,968 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @ozzie14 ,

Its ironic I have had this exact same situation that I answered for 2 times in 2 weeks.

Its because the Month is NOT the month its the Day and Day is not the Day its the Month. Hence why over 12 doesn't work etc.

You have the format backwards for dd and MM
trust me.

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

Was this reply helpful? Yes No
Verified answer

Mike2500 1,247 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

It looks like the inbound date is in a european format, day/month/year, while the system is expecting a US format of month/day/year. (my apologies, I don't know much about international date formats, there's probably better names for this besides european vs US).

the DateValue function has a second argument where you can specify the "language code". If I use your same example, but use a german language code, it works fine:

Text(datevalue(txtDate.Text, "de"),"yyyy-mm-dd")

(of course, pick an appropriate language code, not just the random one I chose)

Was this reply helpful? Yes No
ozzie14 67 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Mike2500 ,

The "de" (I've used "uk") resolved the date conversion problem.

Thanks a tonne..

Was this reply helpful? Yes No