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.

MH3
Post Prodigy
Post Prodigy

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.

MH3
Post Prodigy
Post Prodigy

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 Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (5,914)