cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MH3
Post Prodigy
Post Prodigy

Server Response Error in SQL

Hi,

 

I have an application in which I am storing data into SQL Datasource, I have a gallery in which I am filtering the data between the two date pickers, it was working fine before but now it's showing me error.

I am comparing my TimeSheet Table Date column with my two Date Pickers

 

Untitled.jpg

My TimeSheet Table of SQL:
(Collection)
CollectTimeSheet.jpg

Any Help? I need to filter the data between the selected dates. 
@RandyHayes 

1 ACCEPTED SOLUTION

Accepted Solutions
eka24
Super User
Super User

The main issues has to do with the date column. Per the documentation: https://docs.microsoft.com/en-us/connectors/sql/

  1. "Direct date filters do not work for SQL Server. However, you can create a calculated column that will work. For instance, ALTER TABLE myTable ADD DateAsInt AS (YEAR([date]) * 10000 + MONTH([date]) * 100 + DAY([date])) and then filter on the calculated number column"

So create another column based on the above and use the new Date column in your 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.

View solution in original post

4 REPLIES 4
eka24
Super User
Super User

The main issues has to do with the date column. Per the documentation: https://docs.microsoft.com/en-us/connectors/sql/

  1. "Direct date filters do not work for SQL Server. However, you can create a calculated column that will work. For instance, ALTER TABLE myTable ADD DateAsInt AS (YEAR([date]) * 10000 + MONTH([date]) * 100 + DAY([date])) and then filter on the calculated number column"

So create another column based on the above and use the new Date column in your 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.

Hello Sir @eka24 ,

 

What Should be the Formula for comparing? that new date column with the date pickers?


Your formula is ok. After creating the new date column in the SQL, replace Date with the new Date column

Can you put a text copy of you formula on the platform?

------------

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.

Hi, @eka24 

 

Right now I am using this code in my Gallery Items Property.

SortByColumns(If(var1 && var2,Filter('[dbo].[TimeSheet]',UserId=MyUserEmail),

Filter('[dbo].[TimeSheet]',UserId=MyUserEmail ,(Date>=DatePicker3.SelectedDate) && (Date<=DatePicker1.SelectedDate))),Date,Ascending)



Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (2,965)