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

Ross Turner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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}

Ross Turner

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

beridius

ASKER

I will give this a try thanks guys