Working with date/time values and SQL Server

TL;DR: if you want to use date/time values from a SQL Server table in PowerApps, use the datetimeoffset column type and your life will be easier.

What is the problem?

SQL Server has many different ways (column types) to represent date/time values, and depending on which one is used in the database table, you may have some surprising results when trying to consume that in PowerApps. There have been several threads in the PowerApps community on this issue, so I hope this post will help explain the issue and provide a way to get your app working with your data source.

The problem is that the different data types in SQL Server represent different concepts, and only one of them has a direct mapping to date/time values in PowerApps. Those are the SQL types that represent date/time values:

• datetime (and datetime2 / smalldatetime, which differ only by their range and precision)
• datetimeoffset

The datetime (and friends) column type represent an absolute value of date and an absolute value of time. That's a subtle but important difference from datetimeoffset, which represents a point in time. PowerApps date/time values represent the latter - a point in time, so if you use datetime (and friends) in SQL with PowerApps, then you may have the offset issues.

An example may help to clarify this. If I select a date/time value in PowerApps of 2018-05-22 9:20 (morning), and I'm on the US Pacific Time Zone (UTC - 0700), it will treat the value exactly the same as if someone in London selected the value 2018-05-22 17:20 (5:20 in the afternoon). Even though they are different numeric values (9:20 vs. 17:20), they represent the same instant in time. When using PowerApps (or SQL datetimeoffset columns), you'll have the same value (*) stored in the database.

If you're using datetime columns, however, that concept of "point in time" doesn't exist - it's an absolute value whose location where you're looking at it doesn't matter. A SQL datetime value of '2018-05-15 12:00:00' means "May 15th, 2018, at noon" - regardless of whether we're in the United States, Brazil, Australia, Russia or any other place. For different places the point in time will potentially be different (noon on May 15th in Australia is still May 14th on the United States). This concept (and "absolute time value") doesn't exist natively in PowerApps, so if you're using this type of column in SQL you'll need to adjust how your app handles the value coming from and going to the SQL table.

(*) SQL Server may store the two values differently, for example, as 2018-05-22 09:20:00-0700 vs. 2018-05-22 17:20:00+0100, or even 2018-05-22 16:20:00+00:00 (UTC time) or 2018-05-23 02:20:00+1000 (Sydney, Australia time), but they all still represent the same instant.

But I really need the absolute date/time value - what do I do?

One way to work with absolute date/time values in PowerApps is to store them as strings (char / varchar or nchar / nvarchar columns in SQL Server), using a standard format (yyyy-mm-dd hh:mm:ss). String values are never converted when being transferred over the network to / from the connector, so the value "2018-05-22 09:30:00" always has the same meaning - "May 22nd 2018, 9:30 in the morning". If you want to work with date/time values in your app you can use the DateTimeValue function that will convert the string into the value into the timezone of the app user. The last part is important - that value will always be interpreted in the location of the user. You can then use that value in expressions. For example, if you have a gallery with many events, and a column named 'AbsoluteTimeValue' in the record, you can have a label to let the user know if they need to hurry or not:

If(
    DateDiff(currentTime, DateTimeValue(ThisItem.AbsoluteTimeValue), Minutes) < 30,
    "Less than 30 minutes to the event!",
    "You have time to get ready")

Likewise, when the app needs to save a date/time value back to the data source, it needs to convert it to a string value, which can be done with the Text function:

Patch(
    '[dbo].[tablename]',
    selectedRecord,
    {
        AbsoluteTimeValue: Text(dateTimeValue, "yyyy-mm-dd hh:mm:ss")
    })

Or if you have a date picker / combo boxes set to select the date/time value, like is used in edit forms:

Patch(
    '[dbo].[tablename]',
    selectedRecord,
    {
        AbsoluteTimeValue:
            Text(
              DatePicker1.SelectedDate +
                  Time(
                      Value(dropDownHour1.Selected.Value),
                      Value(dropDownMinute1.Selected.Value),
                      0),
              "yyyy-mm-dd hh:mm:ss")
    })

And this is one way how you can have absolute time behavior in your app.

That's nice, but I already have a table with data in SQL that uses datetime column - what to do now?

Sometimes you don't have the option to choose a string column type - the most common of the cases is when the data already exists, or there are other applications that use the same SQL table with datetime column. If this is the case, you can still use that table in PowerApps, but the data that will likely not be interpreted the way it is defined in the database. For example, if we have the table below:

If we connect to that table, and add a gallery to display the values, you'll likely see something different. Since the data coming from SQL doesn't have any time zone information, PowerApps assumes that it refers to a date in the Coordinated Universal Time (UTC). In my case, my gallery displays the Desc value above the DateTime value, and it will show the following data with 7 hours offset between the values displayed and the value from the database, which is because I'm in the US Pacific Time Zone (UTC-0700).

If we want to see the same value as is displayed in the database, then we need to adjust the time zone offset back so that the data is displayed as if we were on UTC. We can do that via the TimeZoneOffset function, which returns the time zone difference between the local time zone and UTC in minutes. So to update the label with the DateTime value, we need to set its Text property to:

DateAdd(ThisItem.DateTime, TimeZoneOffset(ThisItem.DateTime), Minutes)

Likewise, when saving the date back to the database, you'll need to remove the offset, so that the value that is saved is preserved like in the expression below, where myDateTime is a variable containing the date/time value that you want to save.

Patch(
    '[dbo].[tablename]',
    selectedRecord,
    {
        DateTime: DateAdd(myDateTime, -TimeZoneOffset(myDateTime), Minutes)
    })

And that's another way to work with absolute date/time values in PowerApps

Wrapping up

Hopefully this post will help alleviate the confusion that I've often seen in the community regarding date/time values and PowerApps. As always, feel free to post a new topic in the community if you have questions / comments about PowerApps.