Unanswered

Azure SQL Server - Datetime2 data type showing empty values

(1) Share

Report

Posted on by 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.

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (0)

Sort by

wyotim 2,545 on at

Like (2)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
Meneghino 6,949 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
lero 33 on at

Like (0)

Report
Copy link

Link copied!
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 ) )

Was this reply helpful? Yes No
wyotim 2,545 on at

Like (0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
jreimann 12 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Meneghino 6,949 on at

Like (0)

Report
Copy link

Link copied!

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/

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (2)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
wyotim 2,545 on at

Like (1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Meneghino 6,949 on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
wyotim 2,545 on at

Like (1)

Report
Copy link

Link copied!

@MeneghinoVery true! Thank you for that input.

One of the positives of this issue is that I am getting to go through our data and make sure the data type fits the data it holds. We have experienced the data shift you mentioned. At the time, I just compensated for the difference in timezones, not considering the data type being the real issue. Lesson learned! Thanks again!

Was this reply helpful? Yes No