1

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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Akella225
  • 61
  • 2
  • 8

1 Answers1

2

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)
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • SELECT DATEADD(DAY, 0 - MOD(EXTRACT(WEEKDAY FROM DATE '2020-01-12') + 6, 7), DATE '2020-01-12') FROM RDB$DATABASE Why your code return 6/01/2020? It is wrong. – Akella225 Jan 06 '20 at 12:39
  • @Akella225 I'm assuming the ISO-8601 standard where Monday is the first day of the week, and in ISO-8601, Monday 2020-01-06 and Sunday 2020-01-12 are in the same week. If you're looking for an answer for locales where Sunday is the first day of the week, see the second part of my answer: `dateadd(day, 1 - extract(weekday from d), d)` – Mark Rotteveel Jan 06 '20 at 12:46
  • i need last day of week - sunday 06/01/2020 or 12/01/2020 or 19/01/2020 – Akella225 Jan 06 '20 at 12:49
  • Today is 2020-01-06 and that is a **Monday**. My answer first describes an alternative way to derive Monday, and **then** gives you a way to derive Sunday (using `dateadd(day, 6 - mod(extract(weekday FROM d) + 6, 7), d)`. The code for Sunday is the second code example, not the first. – Mark Rotteveel Jan 06 '20 at 12:49
  • SORY, i need last day of week - sunday 05/01/2020 or 12/01/2020 or 19/01/2020 – Akella225 Jan 06 '20 at 12:51
  • And that is `dateadd(day, 6 - mod(extract(weekday FROM d) + 6, 7), d)`, which for today (Monday 2020-01-06) will yield Sunday 2020-01-12. – Mark Rotteveel Jan 06 '20 at 12:52
  • es, thank you very much, this code works: select DATEADD(DAY, 6 - MOD(EXTRACT(WEEKDAY FROM DATE '2020-01-12') + 6, 7), DATE '2020-01-12') as ldow1 – Akella225 Jan 06 '20 at 12:55