![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 5 of 5 total |
![]() |
Thu, Aug 17 2023 5:56 PM | Permanent Link |
Ian Branch | Hi Team,
Given a week # as a parameter, how can I get the start/finish dates of that week? Regards & TIA, Ian |
Fri, Aug 18 2023 3:36 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Fri, Aug 18 2023 6:35 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Fri, Aug 18 2023 7:30 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Sat, Aug 19 2023 5:17 AM | Permanent Link |
Ian Branch | ... how to add 7..
Hmmm. Hang on I will Google it.. ![]() |
This web page was last updated on Wednesday, August 14, 2024 at 03:12 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |