CODEWITHSUNDEEP
sqlfirebirdfirebird2.5
Chris
Chris

Reputation: 364

Get begin & end date from week number in firebird 2.5

I have week numbers in a column and I want to generate two additional columns which show the week beginning date and the week ending date.

My table:

| week_nr |  year_nr  |
+---------+-----------+
|    2    |   2018    |
|    5    |   2018    |

I want to generate this table

| week_nr  |  year_nr  | week_start  |  week_end   |
+----------+-----------+-------------+-------------+
|    2     |    2018   |  08.01.2018 |  14.01.2018 |
|    5     |    2018   |  29.01.2018 |  04.02.2018 |

I tried to get the dates with the "extract" command but I could not get it working. Does anyone know a solution for Firebird 2.5 ?

Upvotes: 0

Views: 1537

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109079

There is nothing built-in in Firebird to calculate a date from a week number, you will have to write it yourself or find an existing UDF that does it for you (eg F_FIRSTDAYKW in FreeAdhocUDF).

As an example, using a Firebird 3(!) stored function, you can do:

create function mondayOfIsoWeek(isoYear integer, isoWeek integer)
  returns date
as
declare firstJanuary date;
declare firstThursday date;
declare firstWeek integer;
declare targetThursday date;
declare targetMonday date;
begin
  firstJanuary = cast(isoYear || '-01-01' as date);
  -- Thursday in the same week as the first of January
  -- This is either in week 1 or the last week of the previous year
  -- NOTE: 4 is Thursday
  firstThursday = dateadd(day, 4 - extract(weekday from firstJanuary), firstJanuary);
  firstWeek = extract(week from firstThursday);
  -- Already in first week of year, compensate
  if (firstWeek = 1) then
    isoWeek = isoWeek - 1;
  targetThursday = dateadd(week, isoWeek, firstThursday);
  targetMonday = dateadd(day, -3, targetThursday);
  return targetMonday;
end

Firebird 3 stored functions can't be used in Firebird 2.5, but you could use this as the starting point of a stored procedure or execute block that does what you need. If absolutely necessary you could even inline all these calculations to a single expression (but that will be extremely unreadable).

Upvotes: 1

Related Questions