cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AShep Helper I
Helper I

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

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

3 REPLIES 3
Super User
Super User

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

@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?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Community Support
Community Support

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

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:4.JPG

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AShep Helper I
Helper I

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

Hi 

 

 

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

 

 

Helpful resources

Announcements
secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Top Solution Authors
Top Kudoed Authors
Users online (6,269)