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.