First Day of nth Week of the Year in Netezza

Question

How can I find the date of the first day of the nth week of the year? For example, all of the following queries:

SELECT DATE_PART('WEEK', '2019-07-22 01:12:07'::timestamp);
SELECT DATE_PART('WEEK', '2019-07-23 01:12:07'::timestamp);
SELECT DATE_PART('WEEK', '2019-07-24 01:12:07'::timestamp);

give 30, because these three dates are in the 30th week of 2019.

My question is how I can do the opposite of this. Weeks start on Mondays in my version of SQL (not a problem if your starts on Sunday - I can do the adjustment) therefore the first day of week 30 is 2019-07-22 (today, which is Monday). How can I enter the week number, 30, and get the first day of that week, i.e. 2019-07-22 (or 07-21, if the first day of a week is a Sunday in your SQL)

score 0 · Answer 1 · answered Jul 22 '19 at 21:19

0

You have to find the date of the last monday of the previous year and add 7 * {week-number} days to it

P.S: don't forget to account for years that have a 29th of February :)

answered Jul 22 '19 at 21:19

Yonatan Betzer

299
2
8

score 0 · Accepted Answer · answered Jul 23 '19 at 16:25

Turns out, the solution is actually pretty easy. I had to ask it to come up with a solution I guess. Here it is, for the week number 30:

SELECT ('2019-01-01'::timestamp + (30-1)*7 - (DATE_PART('DOW', DATE '2019-01-01')-2))
-- Result: 2019-07-22

I basically start from the first day of the year, add (n-1)*7, and adjust for the first day of the year not being a Monday.

First Day of nth Week of the Year in Netezza

2 Answers2