How to find the days compare with date?

Question

Using SQL Server 2005

Table1

Date

20090501
20090502
20090503
20090504
20090505

...,

I want to find the day compare with date, then i want to skip the date where the day = sunday.

Expected Output

20090501
20090502
20090504
20090505

..,

So 20090503 is skipped, because it is Sunday.

How to make a query?

Answer 1

Lots of ways; here's one;

SELECT column
FROM Table
WHERE DATENAME(dw, column) <> 'Sunday'

Answer 2

Fully localised version (thanks to everyone for pointing that out):

SELECT *
FROM MyTable
WHERE DATEPART(weekday, MyColumn) <> 8 - @@DATEFIRST

Answer 3

Okay, I have no idea what you mean by "date compare with date", but to skip sunday you can use

SELECT    Column
FROM      Table
WHERE     DatePart(weekday, Column) <> 7

You have to check what you have set for DATEFIRST though, because this is important for what DATAPART(weekday) returns for sunday. On an english SQL server, the standard is to return 7, so if you use an english server and haven't changed anything, than it should work.

Answer 4

Well, you can get it into a DateTime format, then once you do that, you could do something like this for a where clause.

WHERE DATEPART(dw, DateColumn) <> 7

This assumes that your SQL Server is configured for english by default.