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.developersdex.com ***

delete from #t
where a < cast(month(current_timestamp) as varchar) + '-1-' +
cast(year(current_timestamp) as varchar)

Show quoteHide quote

It works! Thanks for all your help!



*** Sent via Developersdex http://www.developersdex.com ***

Try:

delete from #t
where a < convert (char (8), DATEADD(MONTH,-1,CURRENT_TIMESTAMP) , 112)


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.developersdex.com ***

Oops, gotta chop off that month part:

delete from #t
where a < convert (char (6), DATEADD(MONTH,-1,CURRENT_TIMESTAMP) , 112) +
'01'

delete from #t
where a < convert (char (8), DATEADD(MONTH,-1,CURRENT_TIMESTAMP) , 112)


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.developersdex.com ***

Try,

delete #t
where a < cast(convert(varchar(6), getdate(), 112) + '01' as datetime)


AMB

Show quoteHide quote where     a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
Show quoteHide quote

SQLDataAdapter.FillSchema() hangs with select on view
Simplifying CHECK constraint
how to rename a temp table
SP plan different from batch
compare two string in SQL Server