select statement doesn't work - SQL Server Forums

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

SQL Server Development (2000)

select statement doesn't work

Author

Topic

PGG123
Yak Posting Veteran

55 Posts

Posted - 2006-12-05 : 17:04:35

There must be something I'm not seeing here. There should be a record (in the table) returned by this select statement: select * from posts
where (sectionid = @id
AND DATEDIFF(dd, getdate(), postdate) > @expiry)

I tried isolating the where clause and it worked with just the first clause sectionid=@id but not with the second clause. What I want returned are records that have been posted more than the @expiry value (which is a value set by the user). @expiry is an int value.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 17:09:51

DATEDIFF function expected the earlier date to be second parameter and latest date the third parameter.

select * from posts
where sectionid = @id
AND DATEDIFF(dd, postdate, getdate()) > @expiry

select * from posts
where sectionid = @id
AND ABS(DATEDIFF(dd, postdate, getdate())) > @expiry

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 17:10:40

Because if you do not have the dates in this order, DATEDIFF reports the difference in days (in your case) as a negative number.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 17:27:28

But for the query to be truly fast and utilize any existing index, try this

SELECT	*
FROM	Posts
WHERE	SectionID = @ID 
	AND PostDate < DATEDIFF(day, DATEDIFF(day, @Expiry, GETDATE()), 1)

With @Expiry = 0 the query selects all records including today.
With @Expiry = 1 the query selects all records including yesterday, but not today.

If @Expiry is negative, ie -1, the query selects all records including tomorrow.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources