How to pull rows only from the weekends?

My table data is like this:

ID   Name salary   date
1     a    100     1/08/2014
2     b    200     2/08/2014
.
.
.
.
26     z   2600    26/8/2014

Now I want to return only rows from Saturday or Sunday, like:

ID   Name salary   date
2    ------------------------
3     -----------------------
9
10
16
17
23
24
30
31 -------------------------

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.

StackExchange Database Administrators Q#74554, answer score: 8