cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Date picker value getting stored with one day less in database table

I am facing an issue in saving date Value from a Date Picker to a database table. The date value saved is on day less than what is selected in the date picker.

 

Details

=======

I have "start date" date picker field with data type as datetime. When I selected the particular date and click 'Save' then the date value is stored into the Database table is one day less than what is selected .i.e. if I select the date " 10/16/2017" in the date picker, then the value stored in the database as "10/15/2017". When I look at the below, I got information this is a known technical issue on the date picker control currently in powerapps. Can you suggest a workaround for this issue. Or is converting the filed to a textbox and accepting date is the only way. Please suggest.

 

https://powerusers.microsoft.com/t5/PowerApps-Forum/Date-displayed-is-one-day-off/td-p/1616

 

Thanks

Krao

3 REPLIES 3
Meneghino
Community Champion
Community Champion

Hi @Anonymous

The issue is that you are storing a date in a datetime column.  You should change your database column type to date, then everything should be fine.  This is because a date of 2017-10-16 is actually converted to a datetime something like 2017-10-15 22:00 UTC depending on where you are.

However, please be aware of issues and work-around for date type columns in some databases:

https://baizini-it.com/blog/index.php/2017/09/28/powerapps-and-azure-sql-database-current-issues/


@Meneghino wrote:

Hi @Anonymous

The issue is that you are storing a date in a datetime column.  You should change your database column type to date, then everything should be fine.  This is because a date of 2017-10-16 is actually converted to a datetime something like 2017-10-15 22:00 UTC depending on where you are.

However, please be aware of issues and work-around for date type columns in some databases:

https://baizini-it.com/blog/index.php/2017/09/28/powerapps-and-azure-sql-database-current-issues/


 

To add to this, the only way we were able to get this to work is by using a datetimeoffset data type and supplying the timezone offset value in the SQL field for our on premise connection to SQL. It's annoying and would be excellent to have resolved on the front end by capturing the TimeZone offset correctly at the app level.

bean
Continued Contributor
Continued Contributor

Hey

 

I recently had this issue (Using Common Data Service). Here is my code to solve? it.

Text(DateAdd(DateTimeValue(I_StartDate.SelectedDate & " " & I_StartTime),

DateDiff(Now(), DateAdd(Now(),TimeZoneOffset(), Minutes),Hours),

Hours), "[$-en-US]yyyy-mm-ddTHH:mm:ssZ")

From this, you can probably just remove the start time and space i have added. 

 

Let me know how you get on.

 

Cheers,

Sean

 

 

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (1,881)