There are two ways of doing this:
One based on the name of the day of the week, the other based on the weekday number.
An example of using the name of the week day (in English, adapt to your local language)
WHERE DATENAME(WEEKDAY, Date) IN ('Saturday', 'Sunday');
Alternatively, you can use
This uses the weekday number for a language independent method of getting the required output.
Sunday = 1, however you can customise this by setting the first day of the week using
WHERE DATEPART(WEEKDAY, Date) IN (1, 7);