SQL - Checking for shifts on Saturday/Sunday over the past 6 months

You can use dayofweek(datecolumn) and see if it is 0 for Sunday or 6 for Saturday...

Double check. The dayofweek() tsql
The main question you have to define if a shift starts or ends in Saturday or Sunday shoukd it be listed?

If a shift by your definition is based on when it starts, the above works.
If you need any shift that hits Saturday or Sunday, you would need to check both
Dayofweek(startdate) or dayofweek(enddate)

Will be the appropriate criteria

Check this recent post

How to get every Mondays for the past 2 years to current date.
https://www.experts-exchange.com/questions/29177765/How-to-get-every-Mondays-for-the-past-2-years-to-current-date.html

to implement to your scenario:

In SQL how would I check for records over a date range including only entries specific weekdays Saturday or Sunday in this case?

DECLARE @STARTOFMONTH DATE
	SET @STARTOFMONTH = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 5, 0)

DECLARE @ENDOFMONTH DATE
	SET @ENDOFMONTH = EOMONTH(GETDATE(),0)

;WITH seq(n) AS 
(
  SELECT 0 UNION ALL SELECT n + 1 FROM seq
  WHERE n < DATEDIFF(DAY, @STARTOFMONTH, @ENDOFMONTH)
)
SELECT
    YEAR(dt) Year, 
	dt as MondayDate, 
    datename(dw, dt) as MondayDateName  
FROM 
    (
        select 
			dateadd(d, n, @STARTOFMONTH) as dt 
            from 
            seq
    ) as dates 
WHERE 
datepart(dw, dates.dt) in (1, 7)
AND dt BETWEEN @STARTOFMONTH AND @ENDOFMONTH
OPTION (MAXRECURSION 200)

Select allOpen in new window

Wrong DB, look at https://docs.microsoft.com/en-us/sql/t-sql/functions/datename-transact-sql?view=sql-server-ver15

Hi EE,

Sorry I didn't complete detail the complete context of my question how to search the above criteria within a standard query looking for records:

select *
from table1 r2h
INNER JOIN [table2] as u
ON [u].UserId= [r2h].UserId
where username = 'Sys' And Filter by date etc.

Select allOpen in new window

The current date field as a format of: 2016-09-27 10:37:40.000

Thank you.

The query is not a substitute for data or an answer

See the link using
And datename(weekday,column_of_datetime) in ('Saturday','Sunday')

Hi Ryan,

Thanks, friend this has got the issue.

Cheers

Many thanks for the assistance guys.