date shouldn't count saturday and sunday

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

date shouldn't count saturday and sunday

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-08-03 : 06:22:52

i have

select count(id) from customers where signedupdate<getdate()-7

How can I get to include the last 7 days but not counting saturday and sunday?

rocknpop
Posting Yak Master

201 Posts

Posted - 2009-08-03 : 06:33:56

Check this: Select DATENAME(dw , getDate())

So your query becomes:
And DATENAME(dw , getDate()) <> 'Saturday' And DATENAME(dw , getDate()) <> 'Sunday'

Check this too, it talks about avoiding functions in where clause:
http://www.mssqltips.com/tip.asp?tip=1236

So maybe you could use a CTE.

Edit:

SELECT COUNT(ID) FROM
(
SELECT ID,signedupdate,
CASE DATENAME(dw ,signedupdate)
WHEN 'Sunday' THEN 0
WHEN 'Saturday' THEN 0
Else 1
END
AS dayName
From customers Where signedupdate<getdate()-7
) AS A WHERE dayName=1

--------------------
Rock n Roll with SQL

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-03 : 07:24:23

see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=130526

KH
[spoiler]Time is always against us[/spoiler]

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-08-05 : 14:50:42

rocknpop - yours doesn't work

in the end what I did was the I check the current day of the week
if it's sunday I do getdate()-8
if 2 I do getdate()-9
3 - getdate()-9
4 getdate()-8
5 getdate()-7
6 getdate()-7
7 getdate()-7

any reason that should not do it?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources