3

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

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.

  • 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 '14 at 13:39
  • Great point, Aaron!! I'll add that to the answer. – Thomas Stringer Aug 21 '14 at 13:41
3

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.

1

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);
  • 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 '14 at 13:47
  • Eugh..sorry..was testing my own logic... – Mark Sinkinson Aug 21 '14 at 13:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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