cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Naireb
Level: Powered On

Date Range Filter for SQL Server Columns

Trying to make a filter so it will get a date picked from the DatePicker, and display all employees that were hired after the selected date. 

Filter('[dbo].[Employee]',
                    DateTimeValue(newHireDate,"en-US")>=DateTimeValue(Text(DatePicker1.SelectedDate,"[$-en-US]mm/dd/yyyy")), 
                    DateTimeValue(newHireDate,"en-US")<=DateTimeValue(Text(todayDate,"[$-en-US]mm/dd/yyyy"))
                )

I am getting a warning and I need to know if theres any way of doing this. 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Naireb
Level: Powered On

Re: Date Range Filter for Employee Hired Date

I actually thought of a filter that will search through a SQL server column without the blue squiggly underline. 

Filter(<your database>,
castDate >= DateDiff(Date(1900,1,1),DateStart_Select.SelectedDate), castDate <= DateDiff(Date(1900,1,1),DateEnd_Select.SelectedDate)
)

 

castDate was created in the SQL view to create a number value from the "beginning of time" or when we started counting it from, 1/1/1900. 3/22/2018 in numeric form would be 43179.


The select statement used to create 'castDate' was this:

CAST(dbo.Shipped_Date AS numeric) AS castDate


This way you can then filter based on a number value and not a DateValue(Text(<insert date>)) which ended up with a Suggestion that said part of this filter may not work due to service limitations.

3 REPLIES 3
Community Support Team
Community Support Team

Re: Date Range Filter for Employee Hired Date

Hi @Naireb,

 

The error might be caught by the mismatch of the date format. Please try:

Filter('[dbo].[Employee]',newHireDate>= DateTimeValue(Text(DatePicker1.SelectedDate),newHireDate<=DateTimeValue(Text(Today())))

For your reference:

https://docs.microsoft.com/en-us/powerapps/functions/function-now-today-istoday

https://docs.microsoft.com/en-us/powerapps/show-text-dates-times

 

Regards,

Mona

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Naireb
Level: Powered On

Re: Date Range Filter for Employee Hired Date

@v-monli-msft
"Invailid argument type. Expecting a Number value" is the error I get if I use the code you gave me. This also threw a suggestion which you can see in the picture attached. 

Naireb
Level: Powered On

Re: Date Range Filter for Employee Hired Date

I actually thought of a filter that will search through a SQL server column without the blue squiggly underline. 

Filter(<your database>,
castDate >= DateDiff(Date(1900,1,1),DateStart_Select.SelectedDate), castDate <= DateDiff(Date(1900,1,1),DateEnd_Select.SelectedDate)
)

 

castDate was created in the SQL view to create a number value from the "beginning of time" or when we started counting it from, 1/1/1900. 3/22/2018 in numeric form would be 43179.


The select statement used to create 'castDate' was this:

CAST(dbo.Shipped_Date AS numeric) AS castDate


This way you can then filter based on a number value and not a DateValue(Text(<insert date>)) which ended up with a Suggestion that said part of this filter may not work due to service limitations.