Datediff confusion - 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

Datediff confusion

Author

Topic

shiloh
Starting Member

48 Posts

Posted - 2007-06-22 : 12:46:03

I have a DELETE statement that is taking ages. I was asked to look into it and immediately noticed the use of function on the column. So its doing a scan even though there is an index on the datecolumn.

HEre's the original statement:

Delete someTable Where datediff(year, DateColumn, getdate()) > 1

I have rewritten it as


Delete someTable Where DateColumn  < dateadd(year, -1, getdate())

When I did a count(*) the counts from previous query didnt match with the new one. Did i miss something?
Perhaps a fresh pair of eyes might help me

thanks,
don.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-22 : 15:16:06

So you like to delete all rows except this year's? Why don't just use 'where DateColumn < '2007-01-01 ...'''? That way, don't need all those calculations.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-06-22 : 16:14:53

The first query will select anything before the current year; the second query will select anything older than one year before the current date. You should decide what you are actually after.

If you want the first, do this:

delete someTable
where
	DateColumn < dateadd(yy,datediff(yy,0,getdate()),0)

CODO ERGO SUM

shiloh
Starting Member

48 Posts

Posted - 2007-06-22 : 16:25:19

quote:
Originally posted by Michael Valentine Jones

The first query will select anything before the current year; the second query will select anything older than one year before the current date. You should decide what you are actually after.

If you want the first, do this:
delete someTable
where
DateColumn < dateadd(yy,datediff(yy,0,getdate()),0)

CODO ERGO SUM

ahh... then how do I explain that the original query resulted in 20 mil rows and the modified query I wrote returned 30 mil rows. The orig query is counting records < 01-01-2007 and mine is counting records < 06-22-2006, I should get less rows right?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-06-22 : 16:46:06

quote:
Originally posted by shiloh

quote:
Originally posted by Michael Valentine Jones

The first query will select anything before the current year; the second query will select anything older than one year before the current date. You should decide what you are actually after.

If you want the first, do this:
delete someTable
where
DateColumn < dateadd(yy,datediff(yy,0,getdate()),0)

CODO ERGO SUM

ahh... then how do I explain that the original query resulted in 20 mil rows and the modified query I wrote returned 30 mil rows. The orig query is counting records < 01-01-2007 and mine is counting records < 06-22-2006, I should get less rows right?

The most likely reason is you did not actually run that code, or made some other mistake. Take a look at it again, or post your actual code and results.

I am assuming that column DateColumn is actually a datetime column, and not some other format.

CODO ERGO SUM

shiloh
Starting Member

48 Posts

Posted - 2007-06-22 : 19:56:37

hi MVJ

I did run the code.


select count(*) 
from MyTable
Where datediff(year, DateColumn, getdate()) > 1

This was the original query and it returned 20661553 rows.
The modified query as you suggested:


select count(*) 
from MyTable
where DateColumn < dateadd(yy,datediff(yy,0,getdate()),0)

returned 41747352 rows.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-06-23 : 14:19:58

quote:
Originally posted by shiloh

hi MVJ

I did run the code.

select count(*)
from MyTable
Where datediff(year, DateColumn, getdate()) > 1

This was the original query and it returned 20661553 rows.
The modified query as you suggested:

select count(*)
from MyTable
where DateColumn < dateadd(yy,datediff(yy,0,getdate()),0)

returned 41747352 rows.

I didn't notice the first time you where using > 1 to select anythig before last year, not this year.

This code should do what you want.


select count(*) 
from MyTable
where DateColumn < dateadd(yy,datediff(yy,0,getdate())-1,0)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources