Removing Weekends?

Hi, i'm new to the SQL game and have been given the task of removing all the weekends in a report so as it only shows the weeks as mon-fri.

I've checked out a few pieces of code but can't seem to get it to work. Anyone able to help?

Determing the Datenumber of the Week depends on the user settings (session settings) of Datefirst, you could also check for the DATEPARTed full day, but this is not language independent, see the following snippet to see how to use the query.

SET Datefirst 1
SELECT 'This is a workday' WHERE Datepart(dw,GETDATE()) < 6 -- 6 is Saturday.

HTH, Jens K. Suessmeyer.

http://www.sqlserver205.de|||

Also consider using a calendar table. Joining on the date you can eliminate weekends, holidays, whatever.

Here is an article I wrote that loads a calendar table:

http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1349.entry

|||The next thing they will ask is to remove holidays, so you should just go ahead and do the calendar file.

My method creates a calendar file with all holidays and Sat/Sun for the year that are NOT holidays. Then this is easy to do in a report:

SET @.calendardays = DATEADD(@.date,-1*(SELECT COUNT(*) FROM HOLIDAYS WHERE HolidayDate >= @.date AND HolidayDate <= @.date),d)

This works well for short periods, but not if you want to subtract weekends between 1/1/1923 and 10/24/2006. Then you need to program it.