CODEWITHSUNDEEP
sqloracle-database
user2119324
user2119324

Reputation: 597

How to find first Thursday of a month using Oracle SQL?

How to find First Thursday of this month using oracle?

select trunc(sysdate, 'MM')firstday from dual;

above is getting firstday of this month

Upvotes: 3

Views: 2168

Answers (2)

Alex Poole
Alex Poole

Reputation: 191570

If you just get the next Thursday after the first day of the month, the after part means that if the 1st is itself a Thursday you will acually get a date a week too late. For instance, 2018-02-01 was a Thursday, and putting that into next_day() gives you 2018-02-08.

You need to get the next Thursday after the last day of the previous month:

next_day(trunc(sysdate, 'MM') - 1, 'THU')

Demo of both values; look at February, March and November in particular:

with t (dt) as  (
  select add_months(date '2018-01-15' , level - 1) from dual connect by level <= 12
)
select next_day(trunc(dt, 'MM'), 'THU') as wrong,
       next_day(trunc(dt, 'MM') - 1, 'THU') as ok
from t;

WRONG       OK
----------  ----------
2018-01-04  2018-01-04
2018-02-08  2018-02-01
2018-03-08  2018-03-01
2018-04-05  2018-04-05
2018-05-03  2018-05-03
2018-06-07  2018-06-07
2018-07-05  2018-07-05
2018-08-02  2018-08-02
2018-09-06  2018-09-06
2018-10-04  2018-10-04
2018-11-08  2018-11-01
2018-12-06  2018-12-06

Upvotes: 1

Jacob
Jacob

Reputation: 14741

Try with the below SQL

SELECT NEXT_DAY(TRUNC(SYSDATE, 'MONTH'),'THURSDAY') FROM DUAL;

SQL Fiddle

Upvotes: 4

Related Questions