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

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (1,781)