Get current day count from current month except holidays

Question

I am a beginner in SQL. I have to fetch current day count(Day Number) from current system month, which should not consider the weekend (Saturday and Sunday).

For example if I am executing the query today (05-Dec-2018) then my output should be 3 (current date is 05-12-2018, here 1st Dec is Saturday and 2nd dec is Sunday. I don't want to include the weekends in this calculation. So Monday (3rd Dec) will be 1, Tue (4th Dec) will be 2 and Wed (5 Dec) will be 3.

Any help for this highly appreciated.

@user7294900 Current date is 05-12-2018. here 1st dec is saturday and 2nd dec is sunday. I dont want to include the weekends in this calculation. So Monday(3 Dec) will be 1, Tue(4 Dec) will be 2 and wed(5 Dec) day will be 3. Hop you are cleare now — Jzl, Dec 05 '18 at 07:54
Basically you want the no of business days from start of month to your day : Use something like [this](https://stackoverflow.com/questions/52977200/count-days-between-two-segments) and pass start date as `TRUNC(urdate,'MM')` and end date as `urdate` — Kaushik Nayak, Dec 05 '18 at 08:17

score 2 · Answer 1 · answered Dec 05 '18 at 10:31

You do not need to use a hierarchical query and can do it independent of the NLS settings using TRUNC( date_value, 'IW' ) to find the start of the ISO week, which is always a Monday.

So:

TRUNC( SYSDATE, 'IW' ) - TRUNC( TRUNC( SYSDATE, 'MM' ), 'IW' )

Will find the number of days between the start of the ISO week containing the first day of the month and the start of the current ISO week. Multiplying this by 5/7 will give the number of week days.

Then all you need to find is how many of those days occurred in the previous month and subtract them. This can be found using:

LEAST( TRUNC( SYSDATE, 'MM' ) - TRUNC( TRUNC( SYSDATE, 'MM' ), 'IW' ), 5 )

and how many days need to be added on for the current week; which is given by:

LEAST( TRUNC( SYSDATE ) - TRUNC( SYSDATE, 'IW' ) + 1, 5 )

So the total can be found using:

SELECT ( TRUNC( SYSDATE, 'IW' ) - TRUNC( TRUNC( SYSDATE, 'MM' ), 'IW' ) ) * 5 / 7
       + LEAST( TRUNC( SYSDATE ) - TRUNC( SYSDATE, 'IW' ) + 1, 5 )
       - LEAST( TRUNC( SYSDATE, 'MM' ) - TRUNC( TRUNC( SYSDATE, 'MM' ), 'IW' ), 5 )
         AS Num_Week_Days
FROM  DUAL;

An example with multiple days:

WITH calendar ( date_value ) AS (
  SELECT DATE '2018-12-01' + LEVEL - 1
  FROM   DUAL
  CONNECT BY LEVEL <= 15
)
SELECT date_value,
       TO_CHAR( date_value, 'DY' ) AS day,
       ( TRUNC( date_value, 'IW' ) - TRUNC( TRUNC( date_value, 'MM' ), 'IW' ) ) * 5 / 7
       + LEAST( TRUNC( date_value ) - TRUNC( date_value, 'IW' ) + 1, 5 )
       - LEAST( TRUNC( date_value, 'MM' ) - TRUNC( TRUNC( date_value, 'MM' ), 'IW' ), 5 )
         AS Num_Week_Days
FROM  Calendar;

Output:

DATE_VALUE  DAY NUM_WEEK_DAYS
----------  --- -------------
2018-12-01  SAT 0
2018-12-02  SUN 0
2018-12-03  MON 1
2018-12-04  TUE 2
2018-12-05  WED 3
2018-12-06  THU 4
2018-12-07  FRI 5
2018-12-08  SAT 5
2018-12-09  SUN 5
2018-12-10  MON 6
2018-12-11  TUE 7
2018-12-12  WED 8
2018-12-13  THU 9
2018-12-14  FRI 10
2018-12-15  SAT 10

wow... impressive! This is how smart you need to be in order to be effective without a calendar table. — Ronnis, Dec 05 '18 at 14:23

Ronnis · Accepted Answer · 2018-12-05T14:03:18.980

1

This type of query is really easy to write if you maintain a calendar table in your database. Here is a query that generates a partial calendar of all days from the start of the month up to and including today. Then we can count the number of weekdays.

select count(*)
  from (select trunc(sysdate, 'MM') - 1 + level as d
          from dual 
       connect by level <= trunc(sysdate, 'DD') + 1 -- Today
                         - trunc(sysdate, 'MM')     -- First day of current month
       )
 -- Exclude weekends 
 where to_char(d, 'DY', 'nls_date_language=american') not in('SAT', 'SUN') 
 ;

edited Dec 05 '18 at 14:03

answered Dec 05 '18 at 08:19

Ronnis

12,593
2
32
52

1

Better use `to_char(d, 'DY', 'nls_date_language=american')` in order to be independent from current user session NLS settings. – Wernfried Domscheit Dec 05 '18 at 09:32
Great suggestion! I've added it – Ronnis Dec 05 '18 at 14:03

Barbaros Özhan · Answer 3 · 2018-12-05T08:27:54.660

1

You might use this sql select statement with connect by level clause :

 select sum(dy) "Total Day"
   from
   (
    select (case when to_char(sysdate-level+1,'D','nls_date_language=turkish') in (6,7)
                 then 0
                 else 1 end ) as dy
      from dual
   connect by level <= to_number(to_char(sysdate,'DD'))
   );

  Total Day
  ---------
      3

You can try the other cases by replacing both sysdate keywords with sysdate + 3, sysdate + 4 , sysdate + 5 ... etc.

edited Dec 05 '18 at 08:27

answered Dec 05 '18 at 08:20

Barbaros Özhan

59,113
10
31
55

Why `nls_date_language=turkish`? – Ori Marko Dec 05 '18 at 08:26
@user7294900 since that's my original language :) you may try other proper cases such as american, english ... those needed to be tried. – Barbaros Özhan Dec 05 '18 at 08:29
@APC yes, but is there a way to express it without using **specific** country settings ? – Ori Marko Dec 05 '18 at 08:34
1

@APC, Result of `TO_CHAR(..., 'D')` depends on `NLS_TERRITORY`, not on `NLS_DATE_LANGUAGE`, Expression `'nls_date_language=turkish'` has not effect in this query. You must use `ALTER SESSION SET NLS_TERRITORY = ... ` or use `DY` / `DAY` – Wernfried Domscheit Dec 05 '18 at 09:28
2

@APC The difference between the date truncated to the start of the day and truncated to the start of the ISO week will do it independent of the NLS settings: `SELECT TRUNC( SYSDATE ) - TRUNC( SYSDATE, 'IW' ) FROM DUAL` – MT0 Dec 05 '18 at 10:04
1

@mt0 - A neat trick, with one correction.. It needs to be `SELECT TRUNC( SYSDATE ) - TRUNC( SYSDATE, 'IW' ) +1 FROM DUAL` otherwise Monday is zero. Which maybe it should be, depending on how good the weekend was ;) – APC Dec 05 '18 at 10:49
@APC the `+1` is dependent on whether you want to include the current day in the range you are counting or not. My answer utilising this does not use the `+1` when counting days in the ISO week before the start of the range but does use it when counting days in the current week so it is situational whether it is necessary. – MT0 Dec 05 '18 at 10:54

score -2 · Answer 4 · edited Dec 05 '18 at 08:53

-2

This should work for SQL Server

SET @today= '2018-12-14'

SET @firstDate = (SELECT DATEADD(month, DATEDIFF(month, 0, @today), 0) AS StartOfMonth);

WITH allDates AS 
(
    SELECT 
            @firstDate AS DT
    UNION ALL
            SELECT 
                DATEADD(dd, 1, DT)
            FROM 
                allDates as S 
            WHERE 
                DATEADD(dd, 1, DT) <= @today
)
SELECT  count(*)  FROM allDates  where  DATENAME(dw,DT) <> 'Saturday' and DATENAME(dw,DT) <> 'Sunday' option (maxrecursion 0)

edited Dec 05 '18 at 08:53

Sashi

2,659
5
26
38

answered Dec 05 '18 at 08:17

Roshan

1

1

Welcome to StackOverflow. It is a pity that this answer is not a suitable response. The questioner has tagged their question `[oracle]`. This means they want a solution **which works for Oracle RDBMS**. MS SQL Server is a different RDBMS, with many syntax differences. This is particularly true for date arithmetic. If you only know MSSQL I suggest you search for questions tagged [`[sql-server]`](https://stackoverflow.com/questions/tagged/sql-server) which you may be better able to answer. – APC Dec 05 '18 at 08:27

Get current day count from current month except holidays

4 Answers4