CODEWITHSUNDEEP
oracle-database
James Carr
James Carr

Reputation: 1

First Monday of last month

I have to get the date of the first Monday of the previous month for an Oracle query.

The ms SQL is 

select dateadd (day, (9 - datepart(dw, eomonth(getdate(), -2)))%7, eomonth(getdate(), -2))

but there is no dateadd function in Oracle.

Upvotes: 0

Views: 2420

Answers (2)

Boneist
Boneist

Reputation: 23588

You can use:

TRUNC(TRUNC(add_months(dt, -1), 'mm') + 6, 'iw')

This subtracts a month from the specified date, truncates it to the 1st of the month, then adds 6 days to it before finally truncating it to the start of the iso week (which is always a Monday).

You need to add 6 to the first of the month to allow the appropriate Monday to count as the first Monday of the month (otherwise you could end up picking the last Monday of the previous month.

You can see it working [here[(https://dbfiddle.uk/?rdbms=oracle_18&fiddle=c25bc32b3223b954e8fcb02b9b76ffd5)

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143103

Here you go:

SQL> alter session set nls_date_language = 'english';

Session altered.

SQL> alter session set nls_date_Format = 'dd.mm.yyyy day';

Session altered.

SQL>
SQL> select next_day(add_months(trunc(sysdate, 'mm'), -1), 'monday') first_monday
  2  from dual;

FIRST_MONDAY
--------------------
02.03.2020 monday

SQL>

What does it do?

  • truncate today's date (SYSDATE) to 1st of month ('mm')
  • subtract 1 month (add_months)
  • use next_day function, along with the 'monday' parameter

Upvotes: 1

Related Questions