Date of last day of week in Firebird 3

This is a date of first day of week (monday) in Firebird 3:

DATEADD(DAY, (EXTRACT(WEEKDAY FROM D - 1) * -1), D)

And how to get the date of the last day of the week (Sunday)?

In Firebird: Mon = 1, Tues = 2, … Sun = 0.

Answer

For ISO-8601 weeks, where Monday is the first day of the week, you can also determine Monday using:

dateadd(day, 0 - mod(extract(weekday FROM d) + 6, 7), d)

The use of mod(extract(weekdays from d) + 6, 7) will make Monday 0, Tuesday 1, etc and Sunday 6 to make the calculations easier.

You can then determine Sunday using:

dateadd(day, 6 - mod(extract(weekday FROM d) + 6, 7), d)

You can easily derive other days of the week this way (eg use 1 - mod(extract(weekday FROM d) + 6, 7) for Tuesday, etc.

On the other hand, if Sunday is the first day of the week (eg as in the US), you can use:

dateadd(day, 0 - extract(weekday from d), d)

and for Monday

dateadd(day, 1 - extract(weekday from d), d)