Unable to update the data from Powerapps to SQL Table data because of different Time Zones

[RatanKP]

Hi All,

Am facing an issue with data filtering and Data update from Powerapps to SQL.

My Issue is:

My SQL Server is in UTC Time Zone and am using my app in UTC +4:00, so when ever am sending data to sql from powerapps my syntax is unable to fetch the data in sql,I used DateTime conversions still am unable to update the data,any suggestions would be perceptible.

Thanks in advance.

[v-jefferni]

Hi @RatanKP ,

Please paste the relevant formulas and the results to further explain the issue you have encountered.

Best regards,

[RatanKP]

Dear Jefferni,

As suggested, below are the syntax are using to update the SQL table data, request to check and do let me know if any further information required.

My Data in SQL is Date = 2023-02-23T04:00:00.000Z , STime =  2023-02-23T18:23:14.010Z;

Set(SDate,LookUp('My Table','Task ID'=ThisItem.'Task ID' && Completed = 0,Text(Date,"[$-en-US]yyyy-mm-ddThh:mm:ss.fffZ"))); Result = 2023-02-23T04:00:00.000Z; 

Set(STime,LookUp('My Table','Task ID'=ThisItem.'Task ID'&& Text(Date,"[$-en-US]yyyy-mm-ddThh:mm:ss.fffZ") = SDate && Completed=0,Text('Start Time', "yyyy-mm-ddThh:mm:ss.fffZ")));  Result = 2023-02-23T18:23:14.010Z;

Set(SDate1,LookUp('My Table','Task ID'=ThisItem.'Task ID' && Completed=0,Text(DateAdd(Date,-TimeZoneOffset(Date),Minutes),"[$-en-US]yyyy-mm-ddThh:mm:ss.fffZ"))); Result = 2023-02-23T08:00:00:000Z;

Set(STime1,LookUp('My Table','Task ID'=ThisItem.'Task ID'&& Text(Date,"[$-en-US]yyyy-mm-ddThh:mm:ss.fffZ") = SDate && Completed=0,Text(DateAdd('Start Time',-TimeZoneOffset('Start Time'),Minutes),"[$-en-US]yyyy-mm-ddThh:mm:ss.fffZ"))); Result = 2023-02-23T22:23:14:010Z;

/*
Patch('My Table',LookUp('My Table','Task ID'= ThisItem.'Task ID' && Text(DateAdd(Date,-TimeZoneOffset(Date),Minutes),"[$-en-US]yyyy-mm-ddThh:mm:ss.fffZ")= SDate && Text(DateAdd('Start Time',-TimeZoneOffset('Start Time'),Minutes),"[$-en-US]yyyy-mm-ddThh:mm:ss.fffZ") = STime && 'Resource No_' = ThisItem.'Resource No_' && Completed=0),
{
'End Time':Now(),
Completed:1
}
);

*/

Patch('My Table',LookUp('My Table','Task ID'= ThisItem.'Task ID' && Text(DateAdd(Date,-TimeZoneOffset(Date),Minutes),"[$-en-US]yyyy-mm-ddThh:mm:ss.fffZ")= SDate1 && Text(DateAdd('Start Time',-TimeZoneOffset('Start Time'),Minutes),"[$-en-US]yyyy-mm-ddThh:mm:ss.fffZ") = STime1 && 'Resource No_' = ThisItem.'Resource No_' && Completed=0),
{
'End Time':Now(),
Completed:1
}
);

[v-jefferni]

Hi @RatanKP ,

These two columns look like of Text data type, is it true? What would be result of below formula:

Patch('My Table',LookUp('My Table','Task ID'= ThisItem.'Task ID' && Date = SDate1 && 'Start Time' = STime1 && 'Resource No_' = ThisItem.'Resource No_' && Completed=0),
{
'End Time':Now(),
Completed:1
}
);

Best regards,

[RatanKP]

Dear Jeffrni,

Those two columns are DateTime in SQL and the data stored in SQL is in "YYYY-MM-DD:HH:MM:SS" format,when am retriving the DateTime Data to Powerapps am receiving/sending the data in format of "MM/DD/YYYY:hh:mm:ss",so am converting the DateTime data to "yyyy-mm-dd:hh:mm;ss" using Text and passing the same format to SQL in order to match the Values.

Formula given by you: Result= Not Working

Patch('My Table',LookUp('My Table','Task ID'= ThisItem.'Task ID' && Date = SDate1 && 'Start Time' = STime1 && 'Resource No_' = ThisItem.'Resource No_' && Completed=0),
{
'End Time':Now(),
Completed:1
}
);

[v-jefferni]

Hi @RatanKP ,

First of all, for criteria within LookUp of Filter functions, you will need the comparison of same data types. So, as you are saying that the two columns are both DateTime types, to retrieve the values:

Set(SDate,LookUp('My Table','Task ID'=ThisItem.'Task ID' && Completed = 0).Date)

Set(STime,LookUp('My Table','Task ID'=ThisItem.'Task ID'&& Date = SDate && Completed=0).'Start Time')

To test the criteria:

Patch('My Table',LookUp('My Table','Task ID'= ThisItem.'Task ID' && Date = SDate && 'Start Time' = STime && 'Resource No_' = ThisItem.'Resource No_' && Completed=0),
{
'End Time':Now(),
Completed:1
}
)

The point is, for date time data types, PowerApps will display date time values based on the time zone of local machine, but the actual saved time is with UTC time zone. For example:

in SQL SERVER TABLE,

Date: 2023-02-23 04:00:00, Start Time:  2023-02-23 18:23:14

Display in your PowerApps:

Date: 2023-02-23 08:00:00, Start Time:  2023-02-23 22:23:14

They are equal actually. Time zone conversion is not needed.

LookUp in PowerApps: LookUp('My table', Date = 2023-02-23 08:00:00, 'Start Time' = 2023-02-23 22:23:14) will get the specific record from SQL table.

Best regards,

[RatanKP]

Dear Jefferni,

As suggested, i tried to change the code, now am unable to fetch the STime with out formating the Date.

Code used:

Set(SDate,LookUp('My Table','Task ID'=ThisItem.'Task ID' && Completed = 0).Date);

Result = 23/02/2023 04:00:00

Set(STime,LookUp('My Table','Task ID'=ThisItem.'Task ID'&& Date = SDate && Completed=0).'Start Time')

Result = Blank

Code Modified:

Set(SDate1,LookUp('My Table','Task ID'=ThisItem.'Task ID'&& Completed=0).Text(Date,"[$-en-US]yyyy-mm-ddThh:mm:ss.fffZ"));

Result = 2023-02-23 04:00:00.000

Set(STime,LookUp('My Table','Task ID'=ThisItem.'Task ID'&& Text(Date,"[$-en-US]yyyy-mm-ddThh:mm:ss.fffZ") = SDate1 && Completed=0).'Start Time');

Result = 23/02/2023 18:23:14

New Patch Code:

Patch('My Table',LookUp('My Table','Task ID'= ThisItem.'Task ID' && Date= SDate && 'Start Time' = STime && 'Resource No_' = ThisItem.'Resource No_' && Completed=0),
{
'End Time':Now(),
Completed:1
}
);

Result = No data Updated

[RatanKP]

Dear All,

Any update on above case???