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

Issue filtering by date - Generated SQL has the day before the selected date

Hi!

I'm creating an interface using a filter by date and the behaviour of application is strange... If I select in the DataPicker the date 01/31/2017, using the function "Filter", the result generated is from the day before the selected: 01/30/2017. It's looks like a simple timezone problem, but I didn't catch the real reason... More informations below:
function: Filter( #DataSource#, databaseDate=DatePicker2.SelectedDate)

The SQL generated for the database, is using da incorrect date... in the example, is using 01/30/2017 instead 01/31/2017

As a test, I create 2 TextFields in the interface that shows the value selected on the DatePicker and I put the following code in the "Text" attribute of each one:

1) TEXT: DatePicker2.SelectedDate
2) TEXT: Text(DatePicker2.SelectedDate, DateTimeZone.UTC)

In the first, the date is incorrect (01/30/2017) and in the second correct (01/31/2017)... that is a signal that is a timezone problem...

But, I don't know how to force during the filter because I can´t transforme the selectedDate in text (the database field "databaseDate" is a dateTimeField)...
function: Filter( #DataSource#, databaseDate=DatePicker2.SelectedDate)

Any idea?

Adctional information: I'm using the Database gateway to connect in my SQL Server (2008 R2)

Thank you so much!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Meneghino
Level 10

Re: Issue filtering by date - Generated SQL has the day before the selected date

Highlighted
Rapha222
Level: Powered On

Re: Issue filtering by date - Generated SQL has the day before the selected date

Hi @Meneghino!

 

Yes... for sure that it's the same issue. In my case, how I'm having the problem in a filter interface I'll try to use DateAdd function in order to change the date selected and generate the right query. When the issue be resolved I will discard the DateAdd function.

 

Thanks for clarifing that I'm not crazy! (at least not for this reason) 🙂 🙂

View solution in original post

5 REPLIES 5
Community Support Team
Community Support Team

Re: Issue filtering by date - Generated SQL has the day before the selected date

Hi @Rapha222,

 

In PowerApps, there are functions available to convert the date string into Date/Time value:

The DateValue function converts a date string (for example, "10/01/2014") to a date/time value.

The TimeValue function converts a time string (for example, "12:15 PM") to a date/time value.

The DateTimeValue functions converts a date and time string (for example, "January 10, 2013 12:13 AM") to a date/time value.

The formular should be:

Filter( #DataSource#,

          databaseDate=DateValue(

                                           Text(DatePicker2.SelectedDate, DateTimeZone.UTC)

                                                   )

          )

More information, see:

DateValue, TimeValue, and DateTimeValue functions in PowerApps

The issue you encountered here is also mentioned in the thread below:

Date displayed is one day off

 

Hope the information above is helpful.

Regards

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

Re: Issue filtering by date - Generated SQL has the day before the selected date

Hi @v-micsh-msft, thanks for responding to my question.

 

I tried it before and retried now again as a test. I converted de date to Text using UTC format and after convert for DateValue and DataValueTime (I tested both again).

 

DataPicker.selectedDate = 31 January 2017

 

#### Part of SQL generated converting to DateTimeValue ###

 (...) from [dbo].[#tableName#] as [_] where 0 = 1  (...)

using: Filter( #DataSource#, databaseDate=DateTimeValue(Text(DatePicker2.SelectedDate, DateTimeZone.UTC)))

 

#### Part of SQL generated converting to DateValue ###

 (...) where convert(date, [_].[fecha]) = convert(datetime2, '2017-01-29 00:00:00') (...)

using: Filter( #DataSource#, databaseDate=DateValue(Text(DatePicker2.SelectedDate, DateTimeZone.UTC)))

 

 

As you can see above, in one case (using DateTimeValue) the query was generated without a Date filter (just with a condition which always is false '1=0') and using DateValue the query contains a date 2 days before was selected on DatePicker.

 

Other test that I did... I changed the filter for a textField in the database and removed the DateValue/DataTimeValue conversion. In this case the query was generated perfectly.

(...) where [_].[databaseTextField] = '2017-01-31T00:00:00.000Z' (...)

 

When I convert the date for Text using DateTimeZone.UTC the date is formatted perfectly but I can´t use text to filter a datetime field in the database. When I convert this textual information for DateValue or DateTimeValue illogically the date is changed for one or two days before.

 

Do you have any additional idea?

 

Thanks!

 

Meneghino
Level 10

Re: Issue filtering by date - Generated SQL has the day before the selected date

Highlighted
Rapha222
Level: Powered On

Re: Issue filtering by date - Generated SQL has the day before the selected date

Hi @Meneghino!

 

Yes... for sure that it's the same issue. In my case, how I'm having the problem in a filter interface I'll try to use DateAdd function in order to change the date selected and generate the right query. When the issue be resolved I will discard the DateAdd function.

 

Thanks for clarifing that I'm not crazy! (at least not for this reason) 🙂 🙂

View solution in original post

anatal52
Level: Powered On

Re: Issue filtering by date - Generated SQL has the day before the selected date

Filter('[dbo].[connect_steps]', datetime >= Date(1900,1,1) )

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 273 members 5,490 guests
Please welcome our newest community members: