November 19, 2007 at 9:02 am
We have a database that collects eventlogs from about 50 servers which gets really large quickly.
I want to run a query against the db to delete by time or date. but I can't seem to get it to work.
2007-01-26 15:33:58.000. this is the structure of the "time" column.
how would I delete the last 10 days, for example
November 19, 2007 at 9:13 am
I can't tell what data type the column "time" is stored at, so I will throw in a CAST for good measure.
Try this kind of syntax:
delete
from tableA
where
datediff(day,cast([time] as datetime), getdate()) <10 -- would delete anything newer than 10 days ago)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 19, 2007 at 9:17 am
Declare @time datetime
Set @time = (select cast((select(left((getdate ()- 10),11))+' '+ '00:00:00.000') as smalldatetime))
Delete from YourTable where DateColumn <= @time
November 19, 2007 at 9:17 am
easiest way is to
select top 1 convert(varchar(23),datetimecolumn,121) as datetimestring
from yourtable
and copy/paste the result.
or use
Declare @wrkDatetime datetime
set @wrkDatetime = dateadd(d, -10, getdate()) -- today - 10 days
select ...
from yourtable
where datetimecolumn <= @wrkDatetime -- indexed ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply