Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 -------------------------
share|improve this question

3 Answers 3

up vote 5 down vote accepted

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.

share|improve this answer
1  
+1 Just make sure SET LANGUAGE ENGLISH; :-) Alternatively, you could use DATEPART and check for the right integer, just make sure SET DATEFIRST is correct. –  Aaron Bertrand Aug 21 at 13:39
    
Great point, Aaron!! I'll add that to the answer. –  Thomas Stringer Aug 21 at 13:41

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.

share|improve this answer

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);
share|improve this answer
    
Your WHERE clause using GETDATE() won't give the desired behavior. That'll test for the current datetime, whether it is Saturday/Sunday. You'd have to specify the Date column there to get the OP's end results. –  Thomas Stringer Aug 21 at 13:47
    
Eugh..sorry..was testing my own logic... –  Mark Sinkinson Aug 21 at 13:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.