What'sUP What'sUP - 2 months ago 17
SQL Question

Remove data using datetime

Goal:

Remove the data in the table Datees by using the variable @remove from dateTbl

The result should be

'2016-01-1 00:00:00.000'
'2016-01-4 00:00:00.000'
'2016-01-5 00:00:00.000'


in table Datees.

It is data

'2016-01-3 00:00:00.000'
'2016-01-4 00:00:00.000'


That shall be removed

Problem:

I just can't find a relevant solution to this context when you have the data as a datetime.

Info:

The content of the variable @remove can be different

CREATE TYPE dateTbl AS TABLE
(
dateID datetime
);


CREATE TABLE Datees
(
dateID datetime
);

INSERT INTO Datees
VALUES
('2016-01-1 00:00:00.000'),
('2016-01-2 00:00:00.000'),
('2016-01-3 00:00:00.000'),
('2016-01-4 00:00:00.000'),
('2016-01-5 00:00:00.000')

//------------------------

DECLARE @remove as dateTbl

INSERT INTO @remove
VALUES
('2016-01-3 00:00:00.000'),
('2016-01-4 00:00:00.000')

select a.*
from @remove a

Answer

You can use a query like this:

DELETE d
FROM Datees AS d
JOIN @remove AS t ON d.dateID = t.dateID

The above query will remove from table Datees all matching records in table variable @remove.

Comments