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
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (3,009)