CODEWITHSUNDEEP
sqloracle-databasedatetruncate
Riot
Riot

Reputation: 63

Find the Friday after Next in Oracle

I have a weird situation where I need to make a DueDate to be the following Friday.

So if the date is Monday, March 11th 2019 then the DueDate is Friday March 22nd.

I am able to do this easily with:

TRUNC(Next_Day(FilingPeriod, 'FRI')) + 7 as DueDate

My issue comes when the day is Friday March 15th 2019 ad it should also have a DueDate of Friday March 22nd, instead it has a DueDate of Friday March 29th.

And I get why. I have been looking for a way to simplify this or throw in a check to determine if the current date is a Friday and go from there.

I have looked for a bit and read similar questions but I still can not seem to find it. Thanks for any help

Upvotes: 3

Views: 72

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Get back to Monday, the first day of the ISO week. Then go plus 4, then plus 7 days:

TRUNC(FilingPeriod, 'iw') + 11

On a sidenote: I'd avoid NEXT_DAY whenever possible. The day name depends on the current language setting and NEXT_DAY has no parameter to override this. This renders the function rather dangerous, because it makes a query fail-prone.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Just subtract 1 from the date before using NEXT_DAY():

TRUNC(Next_Day(FilingPeriod - 1, 'FRI')) + 7 as DueDate

Upvotes: 2

Related Questions