Delete 1 month old records

I need to set up a job which will run the last day of every month to
keep the data in the table for the current month only and delete
everyhthing else (older than 1 month records would be deleted). see the
sample case:
create table #t (a datetime)
insert into #t values (getdate())
insert into #t values ('9/1/2005')
insert into #t values ('8/20/2005')
insert into #t values ('8/8/2005')
insert into #t values ('7/3/2005')
insert into #t values ('6/1/2005')
insert into #t values ('4/1/2004')
delete from #t
where a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
My final result in this case would be only two records (9/2005).
Eeverything else should be deleted. My DELETE stament is not working bc
of the time poartion I guess. Can you correct this SQL?
Thanks for your help.
*** Sent via Developersdex http://www.examnotes.net ***delete from #t
where a < cast(month(current_timestamp) as varchar) + '-1-' +
cast(year(current_timestamp) as varchar)
--Brian
(Please reply to the newsgroups only.)
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:O90arSyrFHA.1204@.TK2MSFTNGP15.phx.gbl...
>I need to set up a job which will run the last day of every month to
> keep the data in the table for the current month only and delete
> everyhthing else (older than 1 month records would be deleted). see the
> sample case:
> create table #t (a datetime)
> insert into #t values (getdate())
> insert into #t values ('9/1/2005')
> insert into #t values ('8/20/2005')
> insert into #t values ('8/8/2005')
> insert into #t values ('7/3/2005')
> insert into #t values ('6/1/2005')
> insert into #t values ('4/1/2004')
>
> delete from #t
> where a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
> My final result in this case would be only two records (9/2005).
> Eeverything else should be deleted. My DELETE stament is not working bc
> of the time poartion I guess. Can you correct this SQL?
> Thanks for your help.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Try:
delete from #t
where a < convert (char (8), DATEADD(MONTH,-1,CURRENT_TIMESTAMP) , 112)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:O90arSyrFHA.1204@.TK2MSFTNGP15.phx.gbl...
I need to set up a job which will run the last day of every month to
keep the data in the table for the current month only and delete
everyhthing else (older than 1 month records would be deleted). see the
sample case:
create table #t (a datetime)
insert into #t values (getdate())
insert into #t values ('9/1/2005')
insert into #t values ('8/20/2005')
insert into #t values ('8/8/2005')
insert into #t values ('7/3/2005')
insert into #t values ('6/1/2005')
insert into #t values ('4/1/2004')
delete from #t
where a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
My final result in this case would be only two records (9/2005).
Eeverything else should be deleted. My DELETE stament is not working bc
of the time poartion I guess. Can you correct this SQL?
Thanks for your help.
*** Sent via Developersdex http://www.examnotes.net ***|||Try,
delete #t
where a < cast(convert(varchar(6), getdate(), 112) + '01' as datetime)
AMB
"Test Test" wrote:

> I need to set up a job which will run the last day of every month to
> keep the data in the table for the current month only and delete
> everyhthing else (older than 1 month records would be deleted). see the
> sample case:
> create table #t (a datetime)
> insert into #t values (getdate())
> insert into #t values ('9/1/2005')
> insert into #t values ('8/20/2005')
> insert into #t values ('8/8/2005')
> insert into #t values ('7/3/2005')
> insert into #t values ('6/1/2005')
> insert into #t values ('4/1/2004')
>
> delete from #t
where a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
> My final result in this case would be only two records (9/2005).
> Eeverything else should be deleted. My DELETE stament is not working bc
> of the time poartion I guess. Can you correct this SQL?
> Thanks for your help.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Oops, gotta chop off that month part:
delete from #t
where a < convert (char (6), DATEADD(MONTH,-1,CURRENT_TIMESTAMP) , 112) +
'01'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23dwJ3cyrFHA.3440@.TK2MSFTNGP10.phx.gbl...
Try:
delete from #t
where a < convert (char (8), DATEADD(MONTH,-1,CURRENT_TIMESTAMP) , 112)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:O90arSyrFHA.1204@.TK2MSFTNGP15.phx.gbl...
I need to set up a job which will run the last day of every month to
keep the data in the table for the current month only and delete
everyhthing else (older than 1 month records would be deleted). see the
sample case:
create table #t (a datetime)
insert into #t values (getdate())
insert into #t values ('9/1/2005')
insert into #t values ('8/20/2005')
insert into #t values ('8/8/2005')
insert into #t values ('7/3/2005')
insert into #t values ('6/1/2005')
insert into #t values ('4/1/2004')
delete from #t
where a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
My final result in this case would be only two records (9/2005).
Eeverything else should be deleted. My DELETE stament is not working bc
of the time poartion I guess. Can you correct this SQL?
Thanks for your help.
*** Sent via Developersdex http://www.examnotes.net ***|||It works! Thanks for all your help!
*** Sent via Developersdex http://www.examnotes.net ***