How to pull rows only from the weekends?
Use the DATENAME() function and specify the datepart as weekday.

select ID, Name, Salary, Date
from dbo.yourTable
where datename(weekday, Date) in ('Saturday', 'Sunday');


As Aaron pointed out, this relies on the language being set to English.  Likewise, you could use the DATEPART() function with weekday and test for Saturday and Sunday values.