CODEWITHSUNDEEP
oracle-databaseplsqlplsqldeveloper
DNamto
DNamto

Reputation: 1362

How to handle weekday calculation with date operation in Oracle

I need to handle specific scenario in StorProc where I need to do date calculation excluding Sat & Sun. Weekends are holiday I need to handle the data within working days. I have implemented below code

if (purchase_date = (trunc(sysdate)-2) or purchase_date = (trunc(sysdate)-1)) Then
specific operation

As I have to exclude Sat & Sun by above implementation is giving wrong results obliviously . For example if today is Monday it has to give me back the date of Friday, my implementation is giving me Saturday or Sunday. I need to calculation with dates for weekdays only. Any help would be appreciated.

Thanks

Upvotes: 0

Views: 344

Answers (1)

MT0
MT0

Reputation: 168741

To compare it to the previous week day, you can use:

IF purchase_date = TRUNC(SYSDATE) 
                 - CASE TRUNC(SYSDATE) - TRUNC(SYSDATE, 'IW')
                   WHEN 0 THEN 3
                   WHEN 6 THEN 2
                   ELSE        1
                   END
THEN
  -- Do something
  NULL;
END IF;

TRUNC(date_value) - TRUNC(date_value, 'IW') will count the number of days since the start of the ISO week (which is always midnight on Monday).

Note: Do not use TO_CHAR(date_value, 'D') in an international setting as it will give a different result depending on which country you run it in (the week starts on Friday in Bangladesh, Saturday in some Middle-Eastern countries, Sunday in America and Monday in most of Europe).

Upvotes: 1

Related Questions