Link to home
Create AccountLog in
Avatar of beridius
beridius

asked on

sql delete

I need to be able to delete a contents of a column but only if it is over 50 weeks old I have the 50 weeks on bit but not sure how to remove contents from it
     (Crtd BETWEEN CAST(CAST(DATEADD(week, - 50, GETDATE()) AS date) AS datetime) AND DATEADD(second, - 1, DATEADD(day, 1, CAST(CAST(DATEADD(week, 
                         - 51, GETDATE()) AS date) AS datetime))))

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Ross Turner
Ross Turner
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Jim Horn
The term DELETE refers to entire rows, not the contents of a column.  
It's an UPDATE to delete contents of a column...

UPDATE YourTable
SET a_column = NULL
WHERE {the 50 weeks on bit}
Hi Beridius

i've create this example for you

http://sqlfiddle.com/#!3/17028/4

Create table test 
(
 VALID_FROM varchar (30),
 VALID_TO varchar (30),
 TXT varchar (30)
)

INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2012-06-01 00:00:00.000','2013-05-31 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2012-06-01 00:00:00.000','2013-05-31 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2012-06-01 00:00:00.000','2013-05-31 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2012-05-14 00:00:00.000','2012-11-13 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2012-05-14 00:00:00.000','2012-11-13 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2013-03-01 00:00:00.000','2013-05-31 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2012-06-01 00:00:00.000','2013-05-31 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2012-04-01 00:00:00.000','2013-04-01 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2012-04-01 00:00:00.000','2013-04-01 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2013-03-01 00:00:00.000','2013-05-31 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2013-03-01 00:00:00.000','2013-05-31 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2012-07-01 00:00:00.000','2013-06-30 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2013-05-01 00:00:00.000','2013-05-31 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2013-05-01 00:00:00.000','2013-05-31 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2013-04-01 00:00:00.000','2013-06-30 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2013-04-01 00:00:00.000','2014-03-31 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2013-04-01 00:00:00.000','2014-03-31 00:00:00.000','Test')

Open in new window


-- Select 

Select
VALID_FROM,
VALID_TO ,
TXT,
DATEDIFF(week,cast([VALID_FROM] as datetime),cast([VALID_TO]as datetime)) AS DiffDate
from test;

-- Update 

update test
set txt = null
where DATEDIFF(week,cast([VALID_FROM] as datetime),cast([VALID_TO]as datetime)) > 51;

-- Select 

Select
VALID_FROM,
VALID_TO ,
TXT,
DATEDIFF(week,cast([VALID_FROM] as datetime),cast([VALID_TO]as datetime)) AS DiffDate
from test;

Open in new window

Avatar of beridius
beridius

ASKER

I will give this a try thanks guys