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

[Anonymous]

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!

[v-micsh-msft]

Hi @Anonymous,

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

[Anonymous]

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 · Accepted solution]

Hi @Anonymous

Please have a look here as well

https://powerusers.microsoft.com/t5/PowerApps-Forum/Please-help-with-UTC/m-p/21244

[Anonymous · Accepted solution]

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) 🙂 🙂

[anatal52]

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