Answered

Date Column from Azure SQL not showing up in Power Apps

(0) Share

Report

Posted on by YongJaco

Hi! I have a date column (data type "date") in my Azure SQL database. For some reason, this column doesn't show in Power Apps when I use a data table or form. I've selected the date column as a field in the data table. When I try to patch the database though, using a date picker input, I am able to update the date column and see it on the database. Power Apps doesn't show the date even if I refresh the source.

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (1)

eka24 20,923 on at

Like (0)

Report

Add another column with datatype Datetime and see if that will show.
------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Was this reply helpful? Yes No
YongJaco 43 on at

Like (0)

Report

Hello! It worked using datetime data type from SQL, however, I just need the date. Also, since the data type in SQL is now datetime, I can no longer save the selected date in date picker .

Was this reply helpful? Yes No
v-xida-msft on at

Like (0)

Report
Hi @YongJaco ,

Do you mean that the Date column could not be displayed in the Data Table inside your canvas app?

Have you tried to display your SQL Table records in a Gallery? Same issue as the Data Table?

Based on the issue that you mentioned, I have made a test on my side, and don't have the issue that you mentioned. Please consider add a Gallery in your app, set the Items property to your SQL Table, then check if same issue occurs.

You could also consider save your SQL Table records into a collection, then check if the Date type column is populated with proper value from your SQL Table:

ClearCollect(TableRecords, '[dbo].[SQLTable]')

Also please consider re-create a new connection to your SQL Table from your canvas app, then check if the issue is solved. Please consider turn off the "Explicit column selection" option in Advanced settings of App settings of your app, then re-load your app, then check if the issue is fixed.

If the issue still exists, please consider convert the "Date" type column in your SQL Table into "Date time", then within your canvas app, re-create a new connection to your SQL Table, then check if the "Date time" column value could be displayed in your app properly. Please use the following SQL syntax to convert your "Date" type column in your SQL Table into "Date time":

ALTER TABLE table_name ALTER COLUMN column_name datetime;

Best regards,

Was this reply helpful? Yes No
eka24 20,923 on at

Like (0)

Report

Well it ok it worked for you.
If you want to show only date, you can use Text(Datefield,"dd-mmm-yyyy) to convert it to date in powerapps.
On saving the datepickcer, what formula are you using that is not making it save?
------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Was this reply helpful? Yes No
YongJaco 43 on at

Like (0)

Report

Hi @eka24 ! Thank you, that worked for me. I converted the data card value using Text(DateValue(Parent.Default), "[$-en-US]dd-mmm-yyyy").

As for the formula I use, I use Patch() and update the row {DateRow: DatePicker.SelectedDate} using date picker. However, since the data type in SQL is now date time, it doesn't allow me to save the changes.

Was this reply helpful? Yes No
YongJaco 43 on at

Like (0)

Report

Hi @v-xida-msft . Looks like I am about to get this sorted. Thanks to @eka24's advice, I converted the date to datevalue. I'm just looking for a solution now on updating the row in SQL using Patch() since the datepicker will only use date while my SQL data type is datetime.

Was this reply helpful? Yes No
eka24 20,923 on at

Like (0)

Report

Have you checked the column ServiceStartDate in SQL that it not the old date column.
ServiceStartDate should be Datetime column.
Can you give the full Patch formula?
------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Was this reply helpful? Yes No
YongJaco 43 on at

Like (0)

Report

Hi @eka24 . Yes, that column is already changed to datetime. Here's the patch formula I use:
Patch(
'[dbo].[UsageReport]',
Defaults('[dbo].[UsageReport]'),
{ServiceStartDate: DateSvcStartDateAdd.SelectedDate}
)

Was this reply helpful? Yes No
Verified answer

v-xida-msft on at

Like (0)

Report
Hi @YongJaco ,

Please consider take a try with the following Patch formula:

Patch( '[dbo].[SQLTable]', LookUp('[dbo].[SQLTable]', 'Primary Key' = BrowseGallery1.Selected.'Primary Key'), { DateTimeColumn: DatePicker1.SelectedDate + Time(Hour(Now()), Minute(Now()), Second(Now())) } )

Note: The 'Primary Key' represents the Primary Key column in your SQL Table, please replace it with actual column name in your SQL Table.

When you use above formula to formula to save date time value from your canvas app to your SQL Table, you may face Time Zone Offset issue. It is an known issue with the date time type column, as an fixed solution, please consider use datetimeoffset type column to store date time value instead of the datetime type in your SQL Table.

Please check and see if the following blog would help in your scenario:

https://powerapps.microsoft.com/hu-hu/blog/working-with-datetime-values-in-sql/

Please consider take a try with above solution, check if the issue is solved.

Best regards,

Was this reply helpful? Yes No
YongJaco 43 on at

Like (0)

Report

Thank you @v-xida-msft! I'm able to update the database now using the Patch() command you have modified.
@eka24 Thank you for you help as well.

Was this reply helpful? Yes No