Datepicker: Selected Date goes back 2 days when saved in database

[AShep]

Hi,

I have a datepicker control which I use to select a date.

The format is set to ShortDate, DateTimeZone to UTC.

I retrieve the date using DatePicker.SelectedDate

When i save this retrieved value, it goes back by 2 days in the database.

I am unsure on what is causing this issue, although something tells me that it could be a timezone issue.

Any thoughts on how to overcome this problem?

Thanks,

AShep

[RandyHayes]

@AShep 

2 days or 2 hours?  If it was 2 hours I'd say you're dealing with a timezone issue.  But, 2 days is something else.

Is the data stored (looking directly at your datasource) actually 2 days back, or just when you view the item again in PowerApps?

Also, what is your datasource?

[v-xida-msft]

Hi @AShep ,

Could you please share a bit more about the database that you mentioned? Is it a SQL Table?

Do you mean that the Date value stored in your database is two days before the selected date in your DatePicker control? Two days is the time offset rather than 2 hours?

I assume that the database that you mentioned is a SQL Table, and you use a datetime type column in your SQL Table to store the selected Date value, is it true?

Actually, it is an known limit when using datetime type column of SQL Table in PowerApps -- If you use datetime (and friends) in SQL with PowerApps, then you may have the time zone offset issues.

Please check the following blog for more details:

https://powerapps.microsoft.com/en-us/blog/working-with-datetime-values-in-sql/

As an fixed solution, you could consider remove the datetime type column from your SQL Table, instead, add a datetimeoffset type column to store the time value. In addition, you need to set the DateTimeZone property of the DatePicker control to following:

DateTimeZone.Local

rather than UTC.

If you have already used the datetime type column value to store the selected date time value from your app, and do not want to add a new datetimeoffset type column in your SQL Table, please take a try with the following workaround:

1. If you use Patch function to suibmit data into your SQL Table:

Set the OnSelect proeprty of the "Submit" button to following:

Patch(
      '[dbo].[tablename]',
       Defaults('[dbo].[tablename]'),
      {
        Column1: "xxx",
        ...,
        DateTime: DateAdd(DatePicker1.SelectedDate, -TimeZoneOffset(myDateTime), Minutes),  /* <-- DatePicker1 represents the DatePicker control in your app */
        ...
      }
)

2. If you use Edit form to submit data into your SQL Table:

within your Edit form, set the Update property of the Datetime field data card to following:

DateAdd(
        DateValue1.SelectedDate + Time(Value(HourValue1.Selected.Value), Value(MinuteValue1.Selected.Value), 0), 
        -TimeZoneOffset(),
        Minutes
)

please consider take a try with above solution, then check if the issue is solved. Also please take a try with the solutions mentioned within above blog I provided.

Best regards,

[AShep]

Hi v-xida-msft,

Thanks for your reply.

Yes, the database at backend is SQL server and the data type for the column is datetime.

I did try to implement by using the below statement in PATCH function:

DateTime: DateAdd(DatePicker1.SelectedDate, -TimeZoneOffset(myDateTime), Minutes),

But this is not working. Just curious, what is myDateTime here? I replaced this with  DatePicker1.SelectedDate but as mentioned not working.

I tried the other work around, added a column NewDate with datetimezoneoffset type, but in this case, the date being saved is one day prior to the one being selected from the datepicker.

Datetimezone is set to Local.

Please advise.

Thanks,

AShep