delete using date time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

delete using date time

Author

Topic

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2009-01-15 : 07:40:11

Hi all

was wondering has anyone ever deleted info from a table using using the time in the date time

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-15 : 07:43:19

yup. just use

DELETE t FROM Table t WHERE t.Datefield< yourvalue

or be more specific

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2009-01-15 : 07:50:40

well we have set the hours and min in date time to 0 so i want to delete all records where the time in date time is 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-15 : 07:54:06

quote:
Originally posted by rjhe22

well we have set the hours and min in date time to 0 so i want to delete all records where the time in date time is 0

ok then use


DELETE t FROM Table t WHERE DATEADD(dd,DATEDIFF(dd,0,t.Datefield),0)=t.Datefield

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2009-01-15 : 08:00:53

ok sorry bout this there has been a slight change in the whole of the delete.
i now have to delete from userid and datetime.

here is what i have done. just dont no how that piece of code will work with what i have done
DELETE FROM dbo.BankNote
WHERE UserID IN
(SELECT TOP 1 um.UserID
FROM Harvest.dbo.UserMap um
WHERE um.SystemUser = 'john')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-15 : 08:02:25

quote:
Originally posted by rjhe22

ok sorry bout this there has been a slight change in the whole of the delete.
i now have to delete from userid and datetime.

here is what i have done. just dont no how that piece of code will work with what i have done
DELETE FROM dbo.BankNote
WHERE UserID IN
(SELECT TOP 1 um.UserID
FROM Harvest.dbo.UserMap um
WHERE um.SystemUser = 'john')

which tables date field you want to check for 0 time?

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2009-01-15 : 08:02:53

the banknote table

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-15 : 08:06:21

What do you mean by delete from userid and datetime ?

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2009-01-15 : 08:07:29

sorry delete from table banknote where the userid is a cretain name and where the time in date time is 0h and 0 m

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2009-01-15 : 08:10:52

ok thanks will try that

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-15 : 08:11:19

[code]DELETE FROM dbo.BankNote
WHERE exists
(SELECT 1
FROM Harvest.dbo.UserMap um
WHERE um.[SystemUser] = 'john' and dbo.BankNote=um.UserID)
and DATEADD(dd,DATEDIFF(dd,0,t.Datefield),0)=t.Datefield[/code]

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-15 : 08:13:29

I changed a bit to what I posted before. Take the latest one. And I hope you are doing all this on a prod box, am always scared of deletes.

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2009-01-15 : 08:13:37

got these errors
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'BN'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'and'.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-15 : 08:15:02

quote:
Originally posted by rjhe22

got these errors
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'BN'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'and'.

Take the code that I posted again.

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2009-01-15 : 08:17:29

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.BankNote" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "t.Datefield" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "t.Datefield" could not be bound.

doing it on a production db

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-15 : 08:24:26

Why on a prod box ?

DELETE FROM dbo.BankNote  
WHERE  exists
(SELECT 1
FROM Harvest.dbo.UserMap um
WHERE um.[SystemUser] = 'john' and BankNote=um.UserID)
and DATEADD(dd,DATEDIFF(dd,0,Datefield),0)=Datefield

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2009-01-15 : 08:26:39

to make sure i dont fuck up the app by deleting wronf stuff. need to be tested first before i would go live with it.

also what does the dd stand for

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-15 : 08:29:50

quote:

Originally posted by sakets_2000

Why on a prod box ?

DELETE FROM dbo.BankNote  
WHERE  exists
(SELECT 1
FROM Harvest.dbo.UserMap um
WHERE um.[SystemUser] = 'john' and BankNote=um.UserID)
and DATEADD(dd,DATEDIFF(dd,0,Datefield),0)=Datefield

how are you relating dbo.BankNote to Harvest.dbo.UserMap?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-15 : 08:32:10

Using user id, had forgotten to mention the col name there,

DELETE FROM dbo.BankNote  
WHERE  exists
(SELECT 1
FROM Harvest.dbo.UserMap um
WHERE um.[SystemUser] = 'john' and BankNote.userid=um.UserID)
and DATEADD(dd,DATEDIFF(dd,0,Datefield),0)=Datefield

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2009-01-15 : 08:36:21

ya i go that part and its working perfect now.cheers for help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources