Azure SQL Server - Datetime2 data type showing empty values

[lero]

My app is not showing datetime2 values from my table. It stopped working without any change in the app or the table.

It doesn't show up in my collections either while date only columns are working properly. The image bellow shows one collection where the date data type (blue) works and the datetime2 (red) doesn't.

I couldn't find any information about current issues with Azure SQL or Powerapps. Any help is appreciated.

[wyotim]

I am having the same issue, though it seems tied to the Sort function for me. If I remove Sort and just collect the table, dates show up. Put Sort back in and date columns are blank after the table is collected. I agree that any help in resolving this issue would be most appreciated!

[Meneghino]

Please see here for current issues with Azure SQL Database.

@wyotimcan you please provide more specific details (i.e. the Sort syntax) so that I can reproduce and update the list?

Thank you.

[lero]

Thanks for the link @Meneghino. I'm aware that there are issues with all kind of datetime data types and SQL Connections. I had to do myself many workarounds to make my app work properly.

The thing is that something has changed. Because the issue started without any changes on my side. My code was working fine two days ago.

I can confirm what @wyotim said. There is something wrong with the sort operator. When I remove the sort it works fine.

My code bellow where HoraInicio is a datetime2 data type.

ClearCollect(CollectLancamento;
    SortByColumns(
         Filter('[dbo].[Horas]'; 
                  dias_lancamento <= DataCorte; 
                  ID_user = Usuario
         ); 
    "HoraInicio"; Ascending
     )
)

[wyotim]

Yes, thank you @Meneghino for the response and the link. My code is much like @lero's. 

ClearCollect(TimeSheetUserCollection, 
          Sort(
                     Filter('[dbo].[TimeSheet]', 
                               EnteredByEmployeeID = CurrentUserID
                     ),
                    TimeSheetDate,
                    Descending
          )
)

In this code, TimeSheetDate is a datetime2 data type but I also tried sorting by ID (the Primary Key) with the same result. When the sort is removed, the dates show up as expected. And to be clear, this code (and others like it) was working fine for weeks before yesterday.

[jreimann]

My PowerApp has the same issue - Azure datetime data was working properly. As of yesterday (1/12/18) there have been no App or database changes, but suddenly the date values no longer appear in the following locations:

- on the edit forms in a text box or a date control

- in galleries if a Sort is applied (dates do appear in galleries without sort)

Please advise on a timeline for resolution.

[Meneghino]

Thank you, I have updated the issues list:

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

[Anonymous]

This started to happen with Datetime field type also (not just Datetime2) when using SortByCollumn(). My app was working fine. Today I noticed an issue all data collected using SortByCollumn() had the Datetime fields empty. SQL Azure Database.

To Fix remove all SortByCollumn() from data tables containing datetime.

[wyotim]

Thank you for posting that info @Anonymous. I was going to test the datetime type today.

For what it is worth, the date data type works fine so if the time element isn't critical it is possible to use that data type instead. I found some places where we had datetime2 where we didn't need time so I converted them and everything went back to normal for those cases.

[Meneghino]

@wyotimit is much better to use date type when time is not needed (i.e. dates of documents, birthdays etc.) so that you do not have issues with time zones.  The effect of time zones on datetime types can be that the date is shifted by one day depending on the relative time zones of the server and the app.