selecting date in where statement

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 2000 Forums

Transact-SQL (2000)

selecting date in where statement

Author

Topic

md1619
Starting Member

1 Post

Posted - 2004-02-16 : 17:08:34

I am trying to write a query that will get all data from yesterday, except when today is Monday, then I need the data from Friday. Below is the query I have so far, how do I go about adjusting for Monday's data?

SELECT CLO,Order_Number,Circuit_ID
FROM Candidate
WHERE ARID = (CONVERT(DATETIME,CONVERT(VARCHAR,(getDate()), 101), 101) - 1)

The convert date function above gets me the date from yesterday rather than the date with time included.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-16 : 18:24:53

One option is to set a local variable:

DECLARE @theday DATETIME

SELECT @theday = 
	CASE 
		WHEN DATEPART(dw,GETDATE()) = 2 THEN DATEADD(dd,-4,GETDATE()) --Monday.. Make the day last Friday
		ELSE DATEADD(dd,-1,getdate()) --Make the day yesterday
	END

SELECT CLO,Order_Number,Circuit_ID
FROM Candidate
WHERE ARID = CONVERT(DATETIME,CONVERT(VARCHAR(12),@theday),101)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources