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
Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Government Carousel

New forum: GCC, GCCH, DoD - Federal App Makers (FAM)

In response to the unique and evolving requirements of the United States public sector, Microsoft has created Power Apps US Government.

Users online (1,912)