How do you account for DST in a Filter based on a Date field?

(0) Share

Report

Posted on by MrNappa

241

I have an entity that contains a Date field. How can I properly filter on it? (pure CDS)

Right now, I have something that looks kind of like this:

UpdateContext({ selectedDay: DateAdd( Today(), 7, Days ) });
Set( planning, First( Filter( Projects, Datum=selectedDay ) ) );

However, in about 2 weeks DST will kick in (Netherlands) and suddenly the filter doesn't return a value any more.

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (0)

Sort by

v-yutliu-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @MrNappa ,

Do you want to filter date based on DST?

Since DST is earlier one hour than standard time, I suggest you firstly transfer local time(DST) to standard time.

Then compare the transferred time with the field in CDS.

Try this formula:

pdateContext({ selectedDay: DateAdd( Today(), 167, Hours ) }); Set( planning, First( Filter( Projects, Datum=selectedDay ) ) );

//167=7*24-1

DateAdd( Today(), 167, Hours ) means that adds 7 days and minutes 1 hour.

Local time is earlier 1 hour than standard time, so you need to minutes 1 hour to transfer to standard time.

Best regards,

Was this reply helpful? Yes No
MrNappa 241 on at

Like (0)

Report
Copy link

Link copied!
Thanks for the suggestion, @v-yutliu-msft .
Actually, I used the following workaround for now:
Set( planning, First( Filter( Projects, Datum>=DateAdd(selectedDay, -1, Hours), Datum<=DateAdd(selectedDay, 1, Hours) ) ) );
That would also work when looking ahead in the other direction. However, it doesn't feel like the most robust piece of code. I guess that's just the limitation of having a date field that's internally stored with also a time component

Was this reply helpful? Yes No
v-yutliu-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @MrNappa ,

Yes, you are right.

If you save date and time together,date value will be affected by time value.

So you need to use DateAdd function to transfer time to avoid the effect.

Do you have any other problems?

If not, could you change your issue status to "Answered"?

Thanks!

Best regards,

Was this reply helpful? Yes No