compare date - 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

compare date

Author

Topic

java148
Yak Posting Veteran

63 Posts

Posted - 2012-01-11 : 10:20:57

how to compare date ? for example, I want to delete all records in April, 2006 . effective type is datetime.

Do we have a simpler way to do this ?

Thanks


delete from Price p where 
DATEPART(YEAR, p.effective) = 2006 and 
DATEPART(MONTH, p.effective) = 4

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-11 : 10:25:41

It may be more efficient to do it like this:

delete from Price where 
effective >= '20060401' and effective < '20060501';

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 11:18:27

If you've index on effective field try to use Sunita's suggestion as it has more chances of using index compared to yours as you're applying function over column which will make it non-sargable

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-11 : 11:23:34

quote:
Originally posted by java148

how to compare date ? for example, I want to delete all records in April, 2006

The Oppies always scare me when they say stuff like this

Make sure you have a backup

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/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-01-12 : 03:28:03

You can find N number of such example codes h

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-01-12 : 03:28:04

You can find N number of such example codes here
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-iii.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources