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
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

Top Kudoed Authors
Users Online
Currently online: 75 members 3,764 guests
Please welcome our newest community members: