CODEWITHSUNDEEP
sqlsql-servert-sqldatetime
HelloWorld1
HelloWorld1

Reputation: 14108

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-5 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

Upvotes: 0

Views: 73

Answers (3)

Cetin Basoz
Cetin Basoz

Reputation: 23827

Probably this is what you meant:

DECLARE @Datees TABLE ( 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 TABLE ( dateID DATETIME );

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

SELECT  *
FROM    @Datees d
WHERE   NOT EXISTS ( SELECT *
                     FROM   @remove r
                     WHERE  r.dateID = d.dateID );

For removal:

delete
    FROM    @Datees
    WHERE   EXISTS ( SELECT *
                         FROM   @remove r
                         WHERE  r.dateID = [@datees].dateID );

However with DateTime values, it might not be quite easy if the values have time parts too. Then you could utilize casting or converting to string values for comparison. If you have such a need then provide more info.

Upvotes: 0

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

Are you trying this:

delete datees where dateid in (select dateid from @remove)

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

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.

Upvotes: 3

Related Questions