web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Re: Convert string (te...
Power Apps
Unanswered

Re: Convert string (text) date to a date value

(0) ShareShare
ReportReport
Posted on by RatanKP 39

Am facing a serious problem with DateValue('Textinput_TimeIN'.Text), as per my SQL Table the Data is in "YYYY-MM-DD" format and when I fetch the data from SQL to Powerapps am receiving the values as "MM/DD/YYYY"(may be due to SQL Server System Date configuration) and I used Text function to convert to "YYYY/MM/DD" and when am passing the value to date field I got text Conversion error and I used DateValue to convert Text to Date Format and  system is converting the DateFormat automatically to "MM/DD/YYYY", I want to convert the Text to DateFormat in "YYYY/MM/DD" Format, any Suggestions would be appreciated.

 

Thank you in advance for all.

Categories:
Building Power Apps
  • RatanKP Profile Picture
    RatanKP 39 on at

    Am facing a serious problem with DateValue('Textinput_TimeIN'.Text), as per my SQL Table the Data is in "YYYY-MM-DD" format and when I fetch the data from SQL to Powerapps am receiving the Values as "MM/DD/YYYY"(may be due to SQL Server Sytem Date configuration) and I used Text function to convert to "YYYY/MM/DD" and when am passing the value to date field I got text Conversion error and I used DateValue to convert Text to Date Format and  system is converting the DateFormat automatically to "MM/DD/YYYY", I want to convert the Text to DateFormat in "YYYY/MM/DD" Format, any Suggestions would be appreciated.

     

    Thank you in advance for all.

    datevalue error.png
  • iAm_ManCat Profile Picture
    iAm_ManCat 18,256 Most Valuable Professional on at

    This post was moved to a fresh thread here as you were unlikely to get an answer by asking a question against a solution that was solved over a year ago that does not relate to your situation

     

    Could you explain the issue in smaller steps, with pictures/screenshots at each step - what value are you passing to the date field - you may need to convert this before passing it - also yo u said you are using the Text function to convert it to yyyy/mm/dd but that's not a valid format for a default date picker - this may be valid for your datasource, but that means you need to change the Update value of your datacard to change the value when written back to your datasource but the value the datepicker uses will need to be in a format that power apps recognises.

  • RatanKP Profile Picture
    RatanKP 39 on at

    Hai @iAm_ManCat ,

    Sorry for posting my issue in this expired chat, as am new to powerapps am just gone through all the blogs and replying.

    I got the solution for the mentioned issue but now am facing new issue as mentioned below with proper syntaxes.

    Solution I found is, as I mentioned, I want to fetch the DateTime value from SQL which is in "YYYY-MM-DD" format and when I got the value it is in "MM/DD/YYYY" Format, so I used Text(MyVar,"YYYY-MM-DD") and to convert it to DATEFormat I used DateValue which is again converting to "MM/DD/YYYY" because of My browser Language Settings,so I changed the browser language settings and Modified the Syntax as below:

     

    1. Set(SDate,LookUp('MyTable','Task ID' = ThisItem.'Task ID' && Completed = 0,Text(DateValue(Date, "en-GB"),"yyyy-mm-dd","es-ES")));       Result = 2023-01-22(as expected)


    2. Set(STime,LookUp('MyTable','Task ID' = ThisItem.'Task ID' && Text(DateValue(Date, "en-GB"),"yyyy-mm-dd","es-ES") = SDate && Completed = 0,'Start Time'));      Result = 2023-01-22 13:09:59(As expected)
    3. Patch('MyTable',LookUp('MyTable','Task ID'= 'Task ID' && Text(DateValue(Date, "en-GB"),"yyyy-mm-dd","es-ES") = SDate && Text(DateTimeValue(Value('Start Time'),"yyyy-mm-dd HH:MM:SS")) = Text(DateTimeValue(Value(STime),"yyyy-mm-dd HH:MM:SS")) && Completed = 0),
    {
    'End Time':Now(),
    Completed:1
    }
    );    Result = No Lookup Data Found and am unable to Modify the SQL Data.

    Note : As the Task ID, Date and Start Time are the primary Keys in SQL Table

     

    4. Set(UpdateVar,LookUp('MyTable','Task ID'= 'Task ID' && Text(DateValue(Date, "en-GB"),"yyyy-mm-dd","es-ES") = SDate && Text(DateTimeValue(Value('Start Time'),"yyyy-mm-dd HH:MM:SS")) = Text(DateTimeValue(Value(STime),"yyyy-mm-dd HH:MM:SS")) && Completed = 0,'End Time'));

    Result = 2023-01-22 15:09(As expected).

     

    Here in Step 4 (Set function) the same Lookup logic is working fine and in Patch function Step 3, the LookUp logic is not working, any suggestion Please.

  • iAm_ManCat Profile Picture
    iAm_ManCat 18,256 Most Valuable Professional on at

    'Task ID'= 'Task ID'

    The above will not filter by task ID - this will look at the task id from the list  and then again look at the task ID from the list.

    You should try disambiguate these using this format

    DatasourceName[@'Task ID'] = OtherDataSourceName[@'Task ID']

  • RatanKP Profile Picture
    RatanKP 39 on at

    Then how the exact values are fetching in below lookups,any suggestions please:

    1. Set(SDate,LookUp('MyTable','Task ID' = ThisItem.'Task ID' && Completed = 0,Text(DateValue(Date, "en-GB"),"yyyy-mm-dd","es-ES")));       Result = 2023-01-22(as expected)

     

    even i gave like below:

     

    3. Patch('MyTable',LookUp('MyTable','Task ID'= ThisItem.'Task ID' or "T000001" this is the table value && Text(DateValue(Date, "en-GB"),"yyyy-mm-dd","es-ES") = SDate && Text(DateTimeValue(Value('Start Time'),"yyyy-mm-dd HH:MM:SS")) = Text(DateTimeValue(Value(STime),"yyyy-mm-dd HH:MM:SS")) && Completed = 0),
    {
    'End Time':Now(),
    Completed:1
    }
    );    Result = No Lookup Data Found and am unable to Modify the SQL Data.

    Note : As the Task ID, Date and Start Time are the primary Keys in SQL Table


    2. Set(STime,LookUp('MyTable','Task ID' = ThisItem.'Task ID' && Text(DateValue(Date, "en-GB"),"yyyy-mm-dd","es-ES") = SDate && Completed = 0,'Start Time'));      Result = 2023-01-22 13:09:59(As expected)

  • iAm_ManCat Profile Picture
    iAm_ManCat 18,256 Most Valuable Professional on at

    Yes, using ThisItem.XYZ will also disambiguate, but the example you gave me earlier only had:

    'Task ID' = 'Task ID'

     

    the Formula you've given above is also ambiguous, you have or "T000001" but you ahve not wrapped it in brackets so its considered a separate OR statement, not part of Task ID logic

     

    Also you are re-converting STime, even though you've already extracted it and converted it, but you are not doing the same with SDate, so if we take out the conversion for STime, like this, does it then allow the patch?

    Patch(
 'MyTable',
 LookUp(
 'MyTable',
 'Task ID'= ThisItem.'Task ID' 
 && Text(DateValue(Date, "en-GB"),"yyyy-mm-dd","es-ES") = SDate 
 && Text(DateTimeValue(Value('Start Time'),"yyyy-mm-dd HH:MM:SS")) = STime
 && Completed = 0
 ),
 {
 'End Time':Now(),
 Completed:1
 }
);

     

  • RatanKP Profile Picture
    RatanKP 39 on at

    Dear Mr. @iAm_ManCat,

    Still same, something am missing which I didn't identify, may be DateTimeValue function syntax??? 

    RatanKP_0-1674648405687.png

    RatanKP_1-1674648506878.png

     

  • iAm_ManCat Profile Picture
    iAm_ManCat 18,256 Most Valuable Professional on at

    Hi,

     

    This is not the formula we are discussing - what is updateVar and why is it mentioned here? We are discussing the patch formula - can you paste a screenshot with the patch formula please - it should match what I have sent in my previous message:

     

    Patch(
 'MyTable',
 LookUp(
 'MyTable',
 'Task ID'= ThisItem.'Task ID' 
 && Text(DateValue(Date, "en-GB"),"yyyy-mm-dd","es-ES") = SDate 
 && Text(DateTimeValue(Value('Start Time'),"yyyy-mm-dd HH:MM:SS")) = STime
 && Completed = 0
 ),
 {
 'End Time':Now(),
 Completed:1
 }
);

     

    Thanks,

    Sancho

  • RatanKP Profile Picture
    RatanKP 39 on at

    As suggested, I did all the changes, UpdateVar is a temp variable to verify whether am able to retrieve the exact data or not, below are the screenshots with each values and data to verify and to close the issue asap.

    SDate variable value:

    RatanKP_0-1674715051312.png

     STime Variable value:

    RatanKP_1-1674715051532.png

    DateValue Function Value while retrieving STime value:

    RatanKP_7-1674717795918.png

    DateValue in Patch Function:

    RatanKP_6-1674717644850.png

    Text(DateValue) in Patch Function:

    RatanKP_0-1674718210356.png

    DateTimeValue in Patch Function:

    RatanKP_4-1674715051250.png

  • iAm_ManCat Profile Picture
    iAm_ManCat 18,256 Most Valuable Professional on at

    Ok, so again, none of these formulae are the one I provided. You need to stop trying to use Text/DateTimeValue/etc against STime and SDate in your patch ,as you have already done that while setting them as variables

     

    Patch(
 'MyTable',
 LookUp(
 'MyTable',
 'Task ID'= ThisItem.'Task ID' 
 && Text(DateValue(Date, "en-GB"),"yyyy-mm-dd","es-ES") = SDate 
 && Text(DateTimeValue(Value('Start Time'),"yyyy-mm-dd HH:MM:SS")) = STime
 && Completed = 0
 ),
 {
 'End Time':Now(),
 Completed:1
 }
);

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the July Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
11manish Profile Picture

11manish 393 Super User 2026 Season 2

#2
Mohsin Ali Profile Picture

Mohsin Ali 328

#3
WarrenBelz Profile Picture

WarrenBelz 278 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans