So far you posted several replies, but you haven't addressed this AT ALL. As a business manager, I would be very upset if the 'computer' people invented their own definitions for such things, instead of asking the business users.

This has absolutely nothing to do with Oracle, or with computers in general it is strictly a business convention. So, your job is to ASK YOUR BUSINESS USERS (not to make it up yourself!!) the exact definition of 'week of the month'.

In that case, what belongs to each month? Can you have an incomplete week (less than 7 days) at the beginning of the month? Is a complete week always assigned to a single month, even if not all the days of that week fall within the month? (For example, in some contexts a week always belongs to the month that includes the majority of that week's days so, if 'week' is from Saturday to Friday, and April 1 is a Thursday, then the whole week belongs to March (from March 27 to April 2) because 5 days of that week are in March and only 2 days are in April. With your weeks starting on a Saturday, in general the 1st day of a month will fall somewhere within a week - not always on a Saturday. The last week is fractional (it may have 1, 2 or 3 days - never 7), but that is the only complication. The fifth week of any month starts on the 29th and ends on the last day of the month. That definition, which doesn't help you, is 'simple', in that there is no confusion around the end of one month and the beginning of the other. It doesn't matter the 'week' is from the 1st to the 7th. So, the first week of March 2021 is from 1 March to 7 March you don't need to know (and it is irrelevant) if 1 March is Saturday, Monday or Wednesday. The first 'week of the month' in that model is always from the 1st to the 7th day of the month, no matter what day of the week the 1st day of the month is. As you noticed in your early attempt (and Solomon explained further in the first reply), the 'W' format model will not help you, because it uses a different definition of 'week of the month'. We understand now that 'week' means Saturday through Friday. You still didn't fully clarify your business problem. Now I divide it by 7 (number of days in one week) and take ceiling value which gives me port_date week number of the month. Now I want to calculate number of days between it and port_date which is port_date minus first day of the week of the forst day of the month of port_date + 1 or port_date - (trunc(trunc(port_date,'mm') + 1,'iw') - 1) + 1 or port_date - trunc(trunc(port_date,'mm') + 1,'iw') + 2. Now I want to convert that nearest Monday back to Sunday, so I subtract 1 day: trunc(trunc(port_date,'mm') + 1,'iw') - 1 which gives me first day of the week of the forst day of the month of port_date. Therefore I use trunc(trunc(port_date,'mm') + 1,'iw').

I want week to start Sunday so I want to shift dates by 1 day up so Sunday becomes Monday. Trunc(port_date,'mm') - first day of the month. Trunc(trunc(port_date,'mm') + 1,'iw') is the same as trunc(port_date,'mm') 235 what's the purpose of adding 2 here: ceil((port_date - trunc(trunc(port_date,'mm') + 1,'iw') + 2) / 7).1.7K Training / Learning / Certification.165.3K Java EE (Java Enterprise Edition).7.9K Oracle Database Express Edition (XE).3.8K Java and JavaScript in the Database.

Oracle week number starting sunday