Date Comparison - 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

General SQL Server Forums

New to SQL Server Programming

Date Comparison

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-24 : 10:08:27

This is gonna scan


   SELECT    CASE WHEN getdate() >= DATEADD(dd,90,vce.CertExpirationDate) THEN 'Certification Expiration 90 Day Notice'
                     WHEN getdate() >= DATEADD(dd,60,vce.CertExpirationDate) THEN 'Certification Expiration 60 Day Notice'
                     WHEN getdate() >= DATEADD(dd,30,vce.CertExpirationDate) THEN 'Certification Expiration 30 Day Notice'

CASE WHEN vce.CertExpirationDate < ????????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2012-01-24 : 12:26:05

[code]
SELECT @Days30 = DATEADD(Day, -30, getdate()),
@Days60 = DATEADD(Day, -60, getdate()),
@Days90 = DATEADD(Day, -90, getdate())

SELECT CASE WHEN vce.CertExpirationDate >= @Days90 THEN 'Certification Expiration 90 Day Notice'
WHEN vce.CertExpirationDate >= @Days60 THEN 'Certification Expiration 60 Day Notice'
WHEN vce.CertExpirationDate >= @Days30 THEN 'Certification Expiration 30 Day Notice'
[/code]
Maybe needs proper day-rounding of the @DaysNN variables to be midnight-last-night

Dunno if it helps the SCAN ... this is SELECT clause, but maybe it is more efficient if nothing else.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-01-24 : 12:27:10

Why whould it scan? If the CASE expression was in the WHERE clasue, then it would scan. Or are you trying to put the case expression in the where clause? If so, just reverse things. You might need to change the order of the case expression evaluation to keep it consistant. for example:

vce.CertExpirationDate < DATEADD(DAY, -90, CURRENT_TIMESTAMP)

Kristen
Test

22859 Posts

Posted - 2012-01-24 : 12:34:21

"Why whould it scan? "

A covering-index available (but not used) perhaps?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-24 : 13:29:08

That's in the select clause, it'll have no effect on whether SQL can seek or scan the index, that's determined by what's in the where clause.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2012-01-24 : 14:40:09

Might SQL not use an index that satisfies the WHERE clause because it doesn't COVER the SELECT - i.e. query planner decides that a SCAN is more efficient because of the columns needed for the SELECT? Or will SQL always use an index that is ideal for the WHERE/JOIN clause(s) and then do a bookmark-lookup to get the data (if the index doesn't adequately COVER the SELECT / rest of the query) ?

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-24 : 14:42:44

well it's in the predicate as well..sorry I didn't mention it

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-24 : 14:57:33

quote:
Originally posted by Kristen

Might SQL not use an index that satisfies the WHERE clause because it doesn't COVER the SELECT - i.e. query planner decides that a SCAN is more efficient because of the columns needed for the SELECT? Or will SQL always use an index that is ideal for the WHERE/JOIN clause(s) and then do a bookmark-lookup to get the data (if the index doesn't adequately COVER the SELECT / rest of the query) ?

Depends on the number of rows selected, but that does not affect whether SQL seeks or scans a particular index, it affects which index SQL uses for the query.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources