Powerapp date field can't display data from SQL table

[mzhang]

Hi,

I have a Powerapp app connected to a SQL server database through data gateway. The app works fine except one strange problem; I have an "Order_date" field in the app (show as a calendar icon). This field won't display data from the corresponding column of SQL server table, it is blank. However this field can be used to insert a date to the SQL server table by selecting a date on the calendar, and the data just inserted to the database table though this field won't be displayed when a application refresh is done. The backhand end date data in the SQL server table was never displayed in the Powerapps app on my hand.

Can someone help me on this? 

Thank you.

[v-micsh-msft]

Hi @mzhang,

What is the field type of the "Order_date" field?

Staff Eric shared the following:

"

There is currently a known issue with the Date field type for SQL Azure where the Date will not display on PowerApps. Our team has identified the issue and the fix should be out soon (within the next release or two). Please use DateTime in the meantime. 

Thanks,

Eric. Software Engineer. PowerApps Staff

"

In this thread: Date, DateTime, SQL AZURE

And currently there is no updates for this issue.

Regards,

Michael

[mzhang]

Michael,

The data type is date. I tested and found out that the data type date won't be displayed on the Powerapp, but that of datetime can be displayed, however in the latter case there is a 7 hour difference between what's shown on the Powerapp and that in the backhand end database table, appearing as another known issue. So far I have not found a complete solution on how to record and use date/datetime in Powerapps application. These issues are not a show stopper to use Powerapps, but they are not minor issues either. I will be happy to use either date or datetime data type, provided they behave correctly. Is there ETA on when these issues can be resolved?

Thanks.

[JRaasumaa]

We use a bunch of dates with a SQL on-premise gateway and the only way I've been able to manipulate or compare dates is to store everything in SQL as a datetimeoffset, might be worth a try.

[Meneghino · Accepted solution]

I think @v-micsh-msft mentioned the fix will be out in the next release or two.  This means in the next month or two. (3-4 weeks between relelases)

I now believe this since other bugs in the SQL server connector are being fixed (i.e. significant figures on money data type) etc.

I would wait for the fix if you can.  Currently I store dates as an integer yyyymmdd, which also gets around the other bug that dates cannot be currently filtered using the > or < operators, but integers can.

I assume this bug will also be fixed at the same time.

[panterra]

Hi,

I am having issues filtering SQL Date Time columns as well.  Has this fix been released yet?

If so, do you know if there are examples of the syntax used for filtering a table using one of its date columns?

Much Thanks,

Sean

[Meneghino]

Hi @panterra

Please see here for the current status:

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

[panterra]

@Meneghino Thank you for the prompt response.  This will definitely help me move forward.

[panterra]

Is there a rough timeline for when functionality will be available to Delegate Filters to SQL Data Sources that include > or <  ?

We are trying to determine whether it would be worth waiting for this functionality or implementing one of  the provided work arounds.

[panterra]

@Meneghino Is there a timeline for when functionality will be available to Delegate Filters that include DateTime columns and greater than or less than to SQL Data Sources?

We are trying to determine whether it would be worth waiting for this functionality or implementing one of  the provided work arounds.