Got it, let's translate that SQL Server logic over to Oracle since we don't have DATEADD or EOMONTH functions built-in (though Oracle has equivalent tools to get the job done).

First, let's recap what your SQL Server query does: it grabs the last day of the month two months ago, calculates how many days to add to get to the next Monday (which ends up being the first Monday of the previous month), then adds those days.

Here are two reliable ways to do this in Oracle, with explanations:

方法1：基于上月第一天计算（更直观） #

This approach starts with the first day of the previous month, then calculates how many days to add to reach the first Monday:

SELECT 
  TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM') + MOD(8 - TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM'), 'IW'), 7) 
  AS first_monday_last_month
FROM DUAL;

Let's break this down:

• TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM'): Gets the first day of the previous month. ADD_MONTHS shifts the date back one month, and TRUNC(..., 'MM') truncates it to the start of that month.
• TO_CHAR(..., 'IW'): Returns the ISO week day number (1 = Monday, 7 = Sunday). This is NLS-independent, so it works no matter what regional settings your database uses (unlike 'D', which can vary by territory).
• MOD(8 - ..., 7): Calculates the number of days needed to jump from the first day of the month to the first Monday. For example:
  • If the first day is Monday (IW=1), MOD(8-1,7) = 0 (add 0 days)
  • If the first day is Sunday (IW=7), MOD(8-7,7) =1 (add 1 day to get to Monday)

方法2：对应SQL Server的逻辑（直接映射） #

If you want to mirror your SQL Server query as closely as possible, we can use the last day of the month two months ago, just like EOMONTH(getdate(), -2):

SELECT 
  LAST_DAY(ADD_MONTHS(SYSDATE, -2)) + MOD(8 - TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -2)), 'IW'), 7) 
  AS first_monday_last_month
FROM DUAL;

• LAST_DAY(ADD_MONTHS(SYSDATE, -2)): Gets the last day of the month two months prior, which matches EOMONTH(getdate(), -2) in SQL Server.
• The rest of the logic is identical to Method 1: calculate the days to add to reach the first Monday of the previous month.

示例验证 #

Let's test with a real date:

• If today is October 2024, the previous month is September 2024. September 1 was a Sunday (IW=7). The query returns September 2, which is indeed the first Monday of the month.
• If today is August 2024, the previous month is July 2024. July 1 was a Monday (IW=1). The query returns July 1, which is correct.

内容的提问来源于stack exchange，提问作者James Carr