Finding answers on workdays

Cunningham’s Law is the idea that “the best way to get the right answer on the internet is not to ask a question; it’s to post the wrong answer.“.

More often than not I feel that some of the answers provided to questions on the SAP Community platform follow this approach, probably being unaware that there is a name for this strategy.

XKCD Duty calls

Question “SAP HANA Exclude weekends while calculating diff between two dates.” is such a question.

“I have requirement to find out the Number of hour between two dates, but we have to exclude the weekends (Saturday & Sunday).

Example : DATE1 is 19-July and DATE2 is July-26 – Actual diff is – 168 Hours.

Required Output is (168 – 48 Hours (Saturday & Sunday) – 120 Hours.

This I want to achieve using Graphical or Script Cal view, please help me to solve the issue”

The OP did not bother googling for solutions for this very common problem nor does (s)he seem to have access to classic SQL development literature, like “Jeo Celko’s Thinking in Sets“.

This is one of those “throw the requirement over the fence” question and really should have been moderated.
However, as so often, an eager answerer offered his help and provided the following code to solve the problem:

    declare v_i integer;
    declare v_count integer;
sel1 = select
            "0CALDAY" as "CALDAY",
            "Yesterday" as "YESTERSDAY",
             sum("DATE_DIFF") AS "DATE_DIFF" ,           
             sum((select 0 from dummy)) as "HOUR_DIFF",
             sum((select 0 from dummy)) as "WRK_HOUR_DIFF"
             from  "_SYS_BIC"."ZTABLE"
--           where "0CALDAY" >= '20180701'
             GROUP BY "0CALDAY", "Yesterday";
select count(*) into v_count from :sel1;
for v_i in 1..v_count do
    WHILE to_date(:sel1.CALDAY[v_i]) < to_date(:sel1.YESTERSDAY[v_i]) do 
        if( weekday(to_date(:sel1.CALDAY[v_i])) ) < 5 
            sel1.WRK_HOUR_DIFF[v_i] = :sel1.WRK_HOUR_DIFF[v_i]  + 24 ;
            sel1.HOUR_DIFF[v_i]     = :sel1.HOUR_DIFF[v_i]      + 24  ;         
            sel1.HOUR_DIFF[v_i]     = :sel1.HOUR_DIFF[v_i]      + 24  ;         
        end if;
        sel1.CALDAY[v_i] = ADD_DAYS(to_date(:sel1.CALDAY[v_i]), 1) ;
    end while ; 
end for;
select * from :sel1;

Reading through this approach several problems stick out:

  • this really shouldn’t require SQLScript at all and definitively not a loop
  • using ‘SELECT 0 from DUMMY’ to generate a constant value of integer 0 is plain wrong
  • summing constant 0 over a grouping set is plain wrong as well – the result is a constant 0
  • removing the aggregation allows for removing the grouping altogether: a SELECT DISTINCT would do the trick here
  • the number of records in sel1 depends solely on the number of different values in ZTABLE.CALDAY a table that has not been defined
  • this number of different CALDAY values is used to loop and add 24 hours to the sel1 table variable HOUR_DIFF and WRK_HOUR_DIFF to keep a tally of hours between dates, which could have been done with HANA SQL functions directly

This looks very much like the kind of SQL code developers tend to write that are not “at home” with SQL and rather try pushing an imperative programming style into their SQL code.

This code is inefficient, slow and hard to maintain.
In short: don’t do it like this.

Alternative solutions

The first approach for this type of common problem really should be to not trying to develop the solution yourself, but instead to look for existing standard solutions.
The mentioned book uses so-called auxiliary tables for this (see chapter 9.5 Calendar Tables) and so does SAP NetWeaver and also the freestyle solution presented in “Want to Calculate WORKDAYS_BETWEEN without TFACS Table?” .

The core of this solution approach is to maintain a table of all dates that indicates whether or not a given date is a weekend or not. Such a table could even hold the information if a given date has been a working day or a public holiday.

Looking at the requirement of “excluding weekends” from counting hours being able to discern workdays from holidays seem like a close-by requirement.

Let’s stick to the case of simply excluding the weekends which the OP defined as Saturday and Sunday.
When working on such a requirement it is particularly important to consider the calendar context in which the solution should work.
Certain calendars (e.g. Isreali calendar) have Friday and Saturday as their weekends.
When the underlying requirement actually is “hours of working days” this might become more complicated with different calendars. Consider, for example, that daylight saving time shifts effectively add/subtract hours – commonly over weekends, but sometimes also on working days.
The OP hasn’t mentioned anything beyond the description of a mechanical calculation problem, so we cannot know what the underlying requirement really was.

Anyway, a quick solution for the simple counting of hours on days other than Saturday and Sunday would be to use the SAP HANA built-in table “_SYS_BI”.”M_TIME_DIMENSION”.
This table provides dates in different formats such as DATE_SQL as well a day of the week number DAY_OF_THE_WEEK_INT (Monday =0, Sunday=6).

With data generated in the table, the original question can be answered as “number of non-weekend days between the given days multiplied by 24 hours

select count(*) as no_of_workdays,
       count(*) * 24 as no_of_hours
from (
      from "_SYS_BI"."M_TIME_DIMENSION"
      where day_of_week not in (5, 6)
      and date_sql between to_date('19.07.2018', 'DD.MM.YYYY')
      and to_date('26.07.2018', 'DD.MM.YYYY')

6              144

Note how this differs from the OPs calculation by one day.
Checking the inner query gives us:

19/07/2018 3
20/07/2018 4
23/07/2018 0
24/07/2018 1
25/07/2018 2
26/07/2018 3

Which means that the OP did miscalculate the hours in the example, by not including the last day of the given range.
This points to another common trap when dealing with dates and intervals: one needs to be clear about whether interval end dates should be included or excluded.

The same approach can be taken with graphical calculation views:

Example calculation view with filter expression
      count(distinct "DATE_SQL" ) as no_of_workdays,
      count(distinct "DATE_SQL" ) * 24 as no_of_hours


The broader view

I mentioned above that this common problem can be broadened to “separate working days from non-working days”.
The SAP business applications have been dealing with such problems for many decades now and so it’s not surprising that there is indeed a standard solution available for this.
This standard solution comes in the form of “FACTORY CALENDARS”.
SAP Netweaver table TFACS holds one row for each year and each calendar.
Every row contains twelve column with a “bitfield” represented by ‘1’ and ‘0’ characters for each day of the month.
A ‘1’ indicates a working day and a ‘0’ denotes a non-working day.

There are some other columns available, but for our purpose, this is what we need to know.

IDENT JAHR MON01                           MON02 ... MON12
AU    2018 0111100111110011111001111000111 1100111110011111001111100111 ... 0011111001111100111110010010110

Typically this table is re-used or replicated from the SAP NetWeaver environment so that all date calculations are using the same calendar definitions.
If you want to use the table without an SAP NetWeaver system you could use the following commands to create it yourself:

create row table TFACS
( IDENT NVARCHAR(2) not null -- factory calendar
, JAHR NVARCHAR(4) not null -- year stored
, MON01 NVARCHAR(31) not null -- Calendar: days in a month, 1 for working day, 0 for non-working day
, MON02 NVARCHAR(31) not null -- Calendar: days in a month, 1 for working day, 0 for non-working day
, MON03 NVARCHAR(31) not null -- Calendar: days in a month, 1 for working day, 0 for non-working day
, MON04 NVARCHAR(31) not null -- Calendar: days in a month, 1 for working day, 0 for non-working day
, MON05 NVARCHAR(31) not null -- Calendar: days in a month, 1 for working day, 0 for non-working day
, MON06 NVARCHAR(31) not null -- Calendar: days in a month, 1 for working day, 0 for non-working day
, MON07 NVARCHAR(31) not null -- Calendar: days in a month, 1 for working day, 0 for non-working day
, MON08 NVARCHAR(31) not null -- Calendar: days in a month, 1 for working day, 0 for non-working day
, MON09 NVARCHAR(31) not null -- Calendar: days in a month, 1 for working day, 0 for non-working day
, MON10 NVARCHAR(31) not null -- Calendar: days in a month, 1 for working day, 0 for non-working day
, MON11 NVARCHAR(31) not null -- Calendar: days in a month, 1 for working day, 0 for non-working day
, MON12 NVARCHAR(31) not null -- Calendar: days in a month, 1 for working day, 0 for non-working day
, BASIS NVARCHAR(5) not null -- start of factory day numbering
, FENUM NVARCHAR(3) not null -- number of days in year
, WENUM NVARCHAR(3) not null -- number of workdays in year
, LOAD NVARCHAR(1) not null -- load year in bufer flag ('X' = YES)
, primary key (IDENT, JAHR));

As an example I entered one row with the working days of Australia/Victoria in 2018:

insert into tfacs values (
'AU', '2018'
-- 1         2         3
-- 1234567890123456789012345678901
, '0111100111110011111001111000111' -- january
, '1100111110011111001111100111' -- february
, '1100111110011111001111100111100' -- march
, '001111100111110011111001011001' -- april
, '1111001111100111110011111001111' -- may
, '100111110001111001111100111110' -- june
, '0111110011111001111100111110011' -- july
, '1110011111001111100111110011111' -- august
, '001111100111110011111001111100' -- september
, '011110011111001111100111110011' -- october
, '11001111100111110011111001111' -- november
, '0011111001111100111110010010110' -- december
, ' '
, ' '
, ' '
, ' ');

With this table in place, SAP HANA provides SQL function WORKDAYS_BETWEEN (see also the SAP HANA Academy video), that returns the desired number of working days directly:

      WORKDAYS_BETWEEN('AU', to_date('19.07.2018', 'DD.MM.YYYY')
                           , to_date('26.07.2018', 'DD.MM.YYYY')) 
      as "no of working days"
FROM dummy;

no of working days

Note that here, the “between” condition is understood as “workdays computed starting on the 19th and finishing on, but not including, the 26th” (see documentation on this) which seem to be the understanding of the OP.

The benefits of using calendar tables over pure function approaches are clear:

  • it’s easy to understand which days are considered working days and which days are not
  • multiple calendars can be maintained and used in parallel (different years, countries, union agreements, operating shifts, etc.)
  • the SQL/application code does not need to be changed when the calendars need updating

There you go, now you know.


  • Please let me know how to get last working day.

    • Ok, you may want to be more specific here.
      Depending on what method you use to define what a working day is, the method of finding a last working day changes.
      While we are at it: what do you mean by last working day? Last day of the week, month or year? Or the last working day of an employee who is going to leave after consuming all outstanding vacation across all potential working days?
      You see, it really matters what you mean here and what situation you want to model.

      • Thanks for your kind attention Breddemann !

        This should be last working day of a week, For Ex. If Friday is last working day , then it should show as Friday .If Friday is Holiday, then it should show Thursday as last working day else if Thursday is holiday then it should display Wednesday as last working day ..Like this…

        • Ok, sana, I understand your requirement.
          How does your design store the information which day is a working day?
          Do you use a table like


          Or did you create a different calendar aux-table?

  • sana says:

    Hi Breddemann,

    Suppose we have our client/company calendar, How can we manipulate by using this calendar to get Last working day, Thanks!

    • With


      you can add or substract a number of workdays to a given date.
      In order to find the last workday of a month, I would put in the first day of the following month and substract one working day.

      Something similar to this:

      SELECT ADD_WORKDAYS('AU', to_date('02.10.2018', 'DD.MM.YYYY' ), -1)
      FROM dummy;

      02.10. - Tuesday
      01.10. - Labour Day/Queen's Birthday
      30.09. - Sunday
      29.09. - Saturday
      28.09. - Friday (Public Holiday in Melbourne - AFL Grand Final - but not AU-wide)


      And that should do the trick.

  • Thanks Lars ! But how this function(ADD_WORKDAYS) captures value =
    ‘AU’ ?

    Sorry for delayed approach.

  • SIBA says:

    Hi Lars , One quick question,,,

    Can we use Input Parameter in UNION Node ?

  • SIBA says:

    Also what is the difference if we go transport management through

    —Quick search–>Export

    —in Life Cycle management

    Is there any difference if we transport Delivery Units through Export Vs through Life Cycle Management Application in different environments(Dev-Staging-Prod) ?

    • Ok, look, these comments are here to discuss the topics mentioned in the blog posts.
      They are not a Q&A forum;

      As it looks like you’ve got a couple of questions, I recommend posting these to Q&A sites like StackOverflow or the SAP Community Q&A.


  • Siba Panda says:

    May I know is there any difference among Left Outer Join Vs Text Join

    [ One thing I know LOJ is not a language dependent but Text join is a language dependent join…] , Is there anything else ?

  • siba says:

    Okay Thanks for your suggestion.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.