Hi Team,
Given a week # as a parameter, how can I get the start/finish dates of that week?

Regards & TIA,
Ian

Ian


This will need a small function, probably two - one for week start and one for week end since I don't think there's a way to deliver a non scalar result.

Roy Lambert

Ian


I've been looking at it and running into a lot of definitional (almost philosophical) questions. Think of the first day of the year - does the week start on a Monday or what. Some companies will have 53 week years with the last couple of days on week 53 being in (say) 2023 with the first days in 2022.

If you want a generalised scheme I'd go for a small lookup table week#, start date, end date - a massive 53 rows and easy to use/adjust.

If you seriously want a function (its to complex for in-line sql - at least at my level) I'll need more info.

Roy Lambert

Ian


Your starter for 10 - I'm hoping you can work out how to add 7 to get end of week

ALTER FUNCTION "WeekNoStart" (IN "WeekNo" SMALLINT)
RETURNS DATE
BEGIN
DECLARE Day1 INTEGER;
DECLARE Offset INTERVAL DAY;
DECLARE Adjustment INTERVAL DAY;
DECLARE WeekDate DATE;
-- Assume year starts 1/1/..
SET WeekDate = CAST(CAST(CAST( EXTRACT(YEAR FROM CURRENT_DATE)AS INTEGER) AS VARCHAR(4)) +'-1-1' AS DATE) ;

SET Offset = WeekNo * 7;
SET Offset = Offset - 7; /* weeks start at 1 not zero */

SET Day1 = EXTRACT(DAYOFWEEK FROM WeekDate);
CASE
WHEN Day1 = 7 THEN
 SET Adjustment = 1;
WHEN Day1 = 6 THEN
 SET Adjustment = 2;
WHEN Day1 = 5 THEN
 SET Adjustment = 3;
WHEN Day1 = 4 THEN
 SET Adjustment = 4;
WHEN Day1 = 3 THEN
 SET Adjustment = 5;
WHEN Day1 = 2 THEN
 SET Adjustment = 6;
ELSE
 SET Adjustment = 0;
END CASE;

SET WeekDate = WeekDate + Offset + Adjustment;

RETURN WeekDate;

END



Roy Lambert

... how to add 7..
Hmmm.  Hang on I will Google it..