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

[Anonymous]

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

[Meneghino]

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/

[JRaasumaa]

---

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

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