How to pull rows only from the weekends?

Question

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 -------------------------

Answer 1

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.

Answer 2

Everyone has been putting solutions that depend on setting either language or datefirst. However, assuming that you're looking for dates only after 1900-01-01, (which was a Monday)

SELECT ID
       ,Name
       ,Salary
       ,Date
FROM <Table_Name>
WHERE DateDiff(dd, 0, Date) % 7 in (5,6)

Regardless of any settings, since 0 (or 1900-01-01) is a Monday, the modulo will always result in 5 or 6 for Sat and Sun respectively.

Answer 3

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)

SELECT
  ID,
  Name,
  Salary,
  Date
FROM <table_name>
WHERE DATENAME(WEEKDAY, Date) IN ('Saturday', 'Sunday');

Alternatively, you can use DATEPART(WEEKDAY, Date).

This uses the weekday number for a language independent method of getting the required output.

By Default, Sunday = 1, however you can customise this by setting the first day of the week using SET DATEFIRST

SELECT
  ID,
  Name,
  Salary,
  Date
FROM <table_name>
WHERE DATEPART(WEEKDAY, Date) IN (1, 7);