Extract weekend days from date

Question

I have date field and from that date field i am trying to extract only weekends i.e. in my case Saturday and Sunday is weekend.

So how can i extract weekends from date?

If below dates are in weekend then should be like this:

Date         day         working hours
01/01/2019
02/01/2019
03/01/2019
04/01/2019
05/01/2019   weekend     24
06/01/2019   weekend     87
07/01/2019
08/01/2019
09/01/2019
10/01/2019

Data link: https://www.dropbox.com/s/xaps82qyyo6i0fa/ar.xlsx?dl=0

score 2 · Answer 1 · answered Nov 21 '19 at 07:32

You can use WeekDay functon. This function accepts date value/field and return the day of the week. The returned value is in dual format - day name and day number.

So you can create additional field that checks if the day number is >= 5 (day numbers are starting from 0 so Saturday = 5 and Sunday = 6)

RawData:
LOAD 
  AttendanceDay, 
  if(WeekDay(AttendanceDay) >= 5, 1, 0) as isWeekend,
  Employee_ID, 
  WorkingHours
FROM
  [..\Downloads\ar.xlsx]
  (ooxml, embedded labels, table is Attendances_20191119_0838)
;

Resulted table after the reload:

Extract weekend days from date

1 Answers1