How can I change my SQL query to display data only on Saturdays and Sundays during a specific period?
I am running SQL Server 2014
and I have the following request which works fine. However, I need to change it so that it only displays those StayDates
that are Saturdays
and Sundays
for the period specified in the request.
I have looked for several solutions, but I have looked at some complex solutions that are difficult to understand. Some have mentioned the use of codes INTERVAL DAYOFWEEK
, but without a clear explanation that would allow me to test the codes.
This is how my request currently stands:
SELECT ResID, MIN(STAYDATE) AS 'Start Date',MAX(STAYDATE) AS 'End Date'
FROM RStayDate
WHERE StayDate BETWEEN '2014-10-01' AND '2014-12-31'
GROUP BY ResID
Instead of giving me all the records for a specific period specified in the request, I need to change the codes so that the table contains ONLY records related to weekends (Saturdays and Sundays).
source to share
Function DATEPART
:
SELECT ResID, MIN(STAYDATE) AS 'Start Date',MAX(STAYDATE) AS 'End Date'
FROM RStayDate
WHERE StayDate BETWEEN '2014-10-01' AND '2014-12-31' AND DATEPART(dw, STAYDATE) IN(7, 1)
GROUP BY ResID
@Richard mentioned that it depends on the settings, so you should check it out here:
https://msdn.microsoft.com/en-us/library/ms174420.aspx
When datepart is week (wk, ww) or day of the week (dw), the return value depends on the value specified with SET DATEFIRST.
source to share