DATEADD returning odd count of records

Hi,
We have a table here containing over 18 million rows and is updatred to
the tune of about 50,000 rows per day. Once a month I would like to run
a script that will delete any rows older than 18 months. All easy, I
here you say. Well when I do the following:
select count(*) from MyTable
where [Insertion Date] <= DATEADD(m, -18, getdate())
this returns a count of approx. 610,000, however when I workout all the
rows for the month of January 2004 (18 months ago the month I need to
remove) I return a count of approx 812,000
select count(*) from MyTable
where [Insertion Date] >= '20040101' and [Insertion Date] <= '20040131'
Apologies for being a tad vague here (obviously you don't know the ins
and outs of the data) but I was wondering if anyone else out there had
the same issue. The table schema for the first few columns is below:
CREATE TABLE [MyTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Consignment] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Date] [datetime] NULL ,
[Batch ID] [int] NOT NULL ,
[Insertion Date] [datetime] NULL ,
Thanks
qhDATEADD(m, -18, getdate()) is giving you 26th Jan 2004. That means
everything older than or equal to 26th Jan 2004. But your second query is
looking for data between 1st and 31st of Jan, which obviously is going to
have 5 days worth of additional data.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1122371409.746888.44830@.g49g2000cwa.googlegroups.com...
Hi,
We have a table here containing over 18 million rows and is updatred to
the tune of about 50,000 rows per day. Once a month I would like to run
a script that will delete any rows older than 18 months. All easy, I
here you say. Well when I do the following:
select count(*) from MyTable
where [Insertion Date] <= DATEADD(m, -18, getdate())
this returns a count of approx. 610,000, however when I workout all the
rows for the month of January 2004 (18 months ago the month I need to
remove) I return a count of approx 812,000
select count(*) from MyTable
where [Insertion Date] >= '20040101' and [Insertion Date] <= '20040131'
Apologies for being a tad vague here (obviously you don't know the ins
and outs of the data) but I was wondering if anyone else out there had
the same issue. The table schema for the first few columns is below:
CREATE TABLE [MyTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Consignment] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Date] [datetime] NULL ,
[Batch ID] [int] NOT NULL ,
[Insertion Date] [datetime] NULL ,
Thanks
qh|||Hello, Scott
You should notice the fact that "DATEADD(m, -18, getdate())"
returns January 26, 2004 and in your second query you are
comparing with January 31, 2004.
If you want to obtain the last day of the current month,
you can use the following:
SELECT DATEADD(month, DATEDIFF(month, 0, getdate()) + 1, 0) - 1
So your query might be:
select count(*) from MyTable
where [Insertion Date] <= DATEADD(m,DATEDIFF(m,0,getdate())-17,0)-1
Razvan|||Hi Razvan,
Cheers for the reply, your solution sorted it. I didn't expect the
count to be over 200k for the days 26-31st Jan. I think it's myself
who doesn't know his own data!
;o)
Thanks
Scott
Razvan Socol wrote:
> Hello, Scott
> You should notice the fact that "DATEADD(m, -18, getdate())"
> returns January 26, 2004 and in your second query you are
> comparing with January 31, 2004.
> If you want to obtain the last day of the current month,
> you can use the following:
> SELECT DATEADD(month, DATEDIFF(month, 0, getdate()) + 1, 0) - 1
> So your query might be:
> select count(*) from MyTable
> where [Insertion Date] <= DATEADD(m,DATEDIFF(m,0,getdate())-17,0)-1
> Razvan