How To Delete Data Older Than X Days, Without Considering Time

When running the following SQL statements, I get the same results.
Though I need to count only -30 days. Both statements below also
consider the time of the day as well, which is not desired


DELETE FROM MNT_R
WHERE MNT_R.TIMESTAMP < GETDATE()- 30

DELETE FROM MNT_R
WHERE MNT_R.TIMESTAMP < DATEADD(d, -30, GETDATE())


Here is the format of the values in column
MNT_R.TIMESTAMP
2005-08-09 06:06:44.577
2005-08-09 06:06:46.810
2005-08-09 06:06:49.060

So, since data are inserted into the MNT_R table every few seconds, my
delete statement will delete different number of rows, according to the
time of the day it runs.

Can you please post a SQL query that will not give me this headache?

thanx a lot all



ADVERTISEMENT

Delete Records Older Than 5 Days .

Hello ,

I am little confused in writing the exact query i.e filling the correct details in the query .
To simplify let me explain....

I am using the query as

delete MYTABLE
where datediff(dd,loaddate,getdate())>5

I have a table now with loadate column which gets the default date and time . The loadate shows correct date and time when the data was imported in the table .

Now suppose if i want to delete previous 5 days records from today
( for e.g today is 20/08/2002 3:40:00 PM ) ideally it should delete all records which are 5 days older from today . i.e from 20/08/2002 3:40:00 PM to 15/08/2002 3:40:00 PM )
But when i execute the datediff command , it deletes the records previous than 15/08/2002 till 15/08/2002.
The records from 15/08/2002 to 20/08/2002 remain intact .

I am getting some different results .

Am i missing something in the query or i am confused about the calculation of the dates the datediff command performs .

Is the logic correct or i am missing someting important ?

Thanks and Regards
Admin001

View Replies View Related

Delete Records In A Table Older Than 90 Days . Help

Hi ,

I have a scheduled job which does an text file import in my database . The data gets appended in my table every day from this import job .

Since my table is growing every day , i want to truncate the table after the data has been collected for three months i.e 90 days . The table will be empty and the new data will flow in through the import .

Any thoughts how to do it through query and schedule it ???

Thanks

View Replies View Related

Delete Files Older Than 3 Days Using SSIS Tasks

I want to delete all files in a given folder that are older than 3 days from today's date.

I tried using a "Foreach Loop container"  with a "File System" task inside it but found I couldn't access any file properties such as the file creation date. Am I using the wrong task for this job?

 

TIA,

 

Barkingdog

 

P.S. History of the problem:

I used the sql 2005 Database maintenance program to setup our database backup jobs. One day I noticed that the free space on the drive where we keep the dumps had grown small. I found that we had at least 4 weeks of dumps in there, not the 3 days I wanted to keep!  I looked but could not find the "delete file" option in the SSIS package generated by the Database Maintenance Wizard. No wonder the files were piling up.

View Replies View Related

Deleting Backup Files Older Than 2 Days

Friends -
I am looking for a windows script (bat file) to delete backup files which are older than 2 days.

Please provide scripts on this.

Appreciate your support

Cheers :)
Satish

View Replies View Related

Deleting Backup Files Older Than 5 Days Old.

I am using the backup task and backing up a database but want to delete all backup files older than 5 days old.  I am using the file task for this and have built the path in a variable but am trying to use a wildcard for the time.  I am getting illegal character in path.  How can I go about this.

I currently have E:MSSQL.1MSSQLBackupdatabasename_backup_20070309*.bak in my input variable and am trying to delete the file databasename_backup_200703091532.bak

View Replies View Related

Delete Backup Files Older Then...

Ok in SQL 2000 this is within the maintaince plan, where as i have to create a seprate one for sql 2005 being i assume the 'clean up history' I choose the backup andrestore option choose 2 weeks and then run the job once created it runs sucssessfully but yet it dosnt delete a thing. I have backups going back almost 2 months now.
What on earth am i not doing?

Thanks in advanced

View Replies View Related

Maintenance Plan Fails Trying To Delete Files Older Thean One Day. Running SQL Server 2005

I get the following message when I execute a mantenance plan to delete files older than 1 day.
 
Error # -1073548784
 
Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2007-09-30T07:56:09'
" failed with the following error: "Error executing extended stored procedure: Invalid Parameter". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Any help would be appreciated!!

View Replies View Related

Delete 2 Days Old Files - Xp_cmdshell

Folks:

I want to delete files on windows from a directory which are 2 days old. I understand we can do that using xp_cmdshell. Anybody with a script would really help me.


Thanks !

View Replies View Related

Query With Time Period Spanning Two Days

I would like to run queries with data that sometimes span two days. The queries require start and end dates as well as start and end times. The following code works fine if the start time is less than the end time:

select * from tst01 where convert(varchar, [DateTime],126) between '2005-09-15' and

'2006-01-27' and convert(varchar, [DateTime],114) between '09:00:00' and

'17:00:00' order by [DateTime]

However, if I try to run a query where the start time is greater than the end time (e.g., start time 5:00pm on one day until 9:00am the next day), the query returns an empty table.

select * from tst01 where convert(varchar, [DateTime],126) between '2005-09-15' and

'2006-01-27' and convert(varchar, [DateTime],114) between '17:00:00' and

'09:00:00' order by [DateTime]

I need a way to indicate that the start and end times span two days. Can anybody help with this?

View Replies View Related

Returning Time Difference As Days/ Hours/ Minutes

I have written a function that returns the number of Days, Hours and minutes from a given number of minutes. On testinf the results are close but not quite there. Can anyone see where I have gone wrong or is there an easier way of doing this? Code is as follows:

CREATE FUNCTION dbo.GetTimeBetweenLong
(@StartTime DateTime, @EndTime DateTime, @CurrentDate DateTime)
RETURNS VarChar(50) AS
BEGIN
DECLARE @TotalTime Numeric
DECLARE @Minutes Numeric
DECLARE @Hours Numeric
DECLARE @Days Numeric
DECLARE @MinutesInDays Numeric

IF @EndTime IS NULL
BEGIN
SET @Days = DATEDIFF(Day, @StartTime, @CurrentDate)
SET @Hours = DATEDIFF(Hour, @StartTime, @CurrentDate) - (@Days * 24)
SET @Minutes = DATEDIFF(Minute, @StartTime, @CurrentDate) - ((@Days * 24)*60) - (@Hours * 60)
END
ELSE
BEGIN
SET @Days = DATEDIFF(Day, @StartTime, @EndTime)
SET @Hours = DATEDIFF(Hour, @StartTime, @EndTime) - (@Days * 24)
SET @Minutes = DATEDIFF(Minute, @StartTime, @EndTime) - ((@Days * 24)*60) - (@Hours * 60)
END

IF(@Days <0)
BEGIN
SET @Days = @Days - @Days - @Days
END

IF (@Hours < 0)
BEGIN
SET @Hours = @Hours - @Hours - @Hours
END

IF (@Minutes <0)
BEGIN
SET @Minutes = @Minutes - @Minutes - @Minutes
END

RETURN CONVERT(nVarChar(10),@Days) + ' Days, ' + CONVERT(nVarChar(5), @Hours) + ' Hours, ' + CONVERT(nVarCHar(5), @Minutes) + ' Mins'

END

View Replies View Related

SQL Server - Select Records Added Today, Last 3 Days, 7 Days...

Hello,I am writing a query to select records added to a table today, in the last 3 days, in the last 7 days, and so on.Here is what I have (which seems that its not working exactly).   -- total listed today
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 0-- total listed yesterday
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 1-- total listed in the last 3 days
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 3I'd like to be able to select the count for records added within the last X number of days. Can someone please help me out?  Thanks so much in advance.

View Replies View Related

Add At The Same Time Delete

what i want is that everytime i add something to my sell table the stocks table deletes
here is the file i want that everytime i buy something the stocks would lessen..

http://rapidshare.com/files/85111869/add_at_the_same_time_delete.zip.html

View Replies View Related

Add At The Same Time Delete

[QUOTE=Dragon_EPT;14362]what i want is that everytime i add something to my sell table the stocks table deletes

<html>
<body>

<%@ page import="java.sql.*"%>
<%@ page import="java.util.*"%>
<%@ page import="java.util.*"%>
<%
try {
Connection con;
Statement stmt;
ResultSet rs;
String brokers = request.getParameter("brokers");
String stock = request.getParameter("stock");
String qty = request.getParameter("qty");
String price = request.getParameter("price");

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:sherwin","","");
stmt = con.createStatement();

rs = stmt.executeQuery("INSERT INTO sell (brokers,stock,qty,price) VALUES ('"+brokers+"','"+stock+"','"+qty+"','"+price+"')");
rs = stmt.executeQuery("DELETE FROM stocks WHERE brokers = 'one'");


response.sendRedirect("main.jsp");
con.close();
} catch (Exception e){
System.out.println("SQL Exception : " + e.getMessage());
}
response.sendRedirect("main.jsp");
%>

</body>
</html>

[/QUOTE]

View Replies View Related

Making A Row Delete Itself At A Certain Time?

Is there any possible way to make a row delete itself at a certain date and time? I am tring to make an "On_Sale" table and perticular items(rows) must expire at a certain Date and time. I have an idea to delete a row when it expires but it will cause an extra burden on my application. So is it possible to have SQL responsible for deleting a row at a certain time and date?

Also, I have another question:
when I asign a PK Identity to a certain column is there a way I can enforce consecutive order of PK values? For example if I delete a row and the PK value was 5 it will reoder the whole table so all PK values are in consecutive order:


PK-----------Item----------price
1------------XYZ1----------$$$$
2------------XYZ2----------$$$$
3------------XYZ3----------$$$$

when I delete PK 2 this is what happens

PK-----------Item----------price
1------------XYZ1----------$$$$
3------------XYZ3----------$$$$

the PKs are not in consecutive order


What I want is this to happen: when PK 2 is deleted I need it to show as follows

PK-----------Item----------price
1------------XYZ1----------$$$$
2------------XYZ3----------$$$$

The PKs stay in consecutive order.



See how the PK are still in consecutive order?

View Replies View Related

Queue DELETE Time

Hello!

In running some performance tests on a Queue using a message size of ~5KB, we found that we can process (SEND and RECEIVE) on the order of 600 - 800 messages / second.  However, we have found that INSERTs of new messages to the Queue appear to take great precedence over DELETEs of received messages from the queue.  In particular, we found that during heavy use the total size of the Queue (as determined using the sp_spaceused procedure) equals about the number of total messages processed, not the number of messages on the queue.

When we stop sending messages, the overall size of the Queue table appears to decrease slowly, so there is a background process that is obviously doing some work there to clean up the received messages from the Queue.  What I would like to know is if we can affect that background process in any way so that the messages are cleared out more quickly.  The performance has been determined to suffer appreciably once the Queue size grows to greater than about 3GB in size.  We also notice timeouts on the RECEIVE statements when the Queue size is that large.

Thanks for any help --

Robert

View Replies View Related

Delete Multiple Tables At A Time

 
Hi: I have 3 tables namely:
1  Category(CategoryID(int), CategoryName(varchar),
2  SubCategory( CategoryID(int),SubcategoryID(int),SubcategoryName)
3 Productlist (ProductID(int),ProductName(varchar),CategoryID(int), CategoryName(varchar),SubcategoryID(int),SubcategoryName(varchar))
how to delete correspoding subcategories of category from  SubCategory,Productlist  tables using triggers
Ex: Category :TV   Subcategory:ColorTV,Plasma,LCD...Plz Send me the query....
Thanks
 
 

View Replies View Related

How To Delete Records Based On Time?

Hello

I want to delete my record after ten days of the Posted date(field)..how do I do it? I'm able to insert date(short date , long time) to the database. I'm using sql server and C#. this is what I have so far.I would appreciate your help..

Thanks!!!<%@ Page Language="C#"%>
<%@ Import Namespace="System.Data.SqlClient" %>

<script runat=server>

void Page_Load(Object sender , EventArgs e)
{
SqlConnection conPubs;
string strDelete;
SqlCommand cmdDelete;

conPubs = new SqlConnection( @"Server=localhost;Integrated Security=SSPI;database=Book" );
strDelete = "Delete tblbook Where Posted_Date =???????????";
cmdDelete = new SqlCommand( strDelete, conPubs );
conPubs.Open();
cmdDelete.ExecuteNonQuery();
conPubs.Close();
Response.Write("Records Deleted!");
}
</script>
//

</script>
<html>
<head>
</head>
<body>
<form runat="server">
<!-- Insert content here -->
</form>
</body>
</html>

View Replies View Related

How Can I Delete All Rows In A Table At Same Time

hi,
here i am with a table containing 5columns and 100 rows.i want to delete all rows at the same time. pls suggest me a way on this

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

View Replies View Related

CAN I INSERT,DELETE,UPDATE2tables At The Same Time

i've read the transact-sql command,

i known that the select command use to retrieve many fields from many tables with one command

select * from table1,table2

yes,

but i ' ve not seen the way to add,delete or update those fields from those tables with one command...



Is it possible? why?

I don't have any idea , can u help me

I want to know the sql commands , if it's possible

thanks for reply,

mochi

View Replies View Related

Pivot Aging 30 Days, 60 Days && So On

Hi, I have some difficulties in creating an aging report using pivot.  I have try searching the google, & cannot really understand the example.
I need to create an aging report of <30 days, <=60 days, <=90, <=120 , > 120 days & Balance.
I have 1 table called mTrialBalance with fields called TBDate, Credit, Debit.
I have difficulty in finding the expression of creating the fields for pivot the the aging reports with the 30 days, 60 days as header of a column & Amount (Debit - Credit) into the respective columns & the total amount into Balance.
Can someone teach me how to use it?Thanks.

View Replies View Related

Retrieve Data For Working Days Using Date Function

Hi
I would like to return data for working days only. This will need to exclude holidays.For eg In the Month of August we have 31 Days and every 1st day of 1st week is holiday.So my output should retrieve me 31-4=27 .
Any ideas?

Thanks...

View Replies View Related

Delete A Record That Exist In 6 Tables At The Same Time

Hi! Is there a way to delete a record from multiple tables at the same time? Thanks for the help!

View Replies View Related

CAN I Command (INSERT, DELETE, UPDATE) 2 Tables At The Same Time? POSSIBLE? HOW?

i've read the transact-sql command, 
i known that the select command use to retrieve many fields from many tables with one command
select * from table1,table2
yes,
but i ' ve not seen the way to add,delete or update those fields from those tables with one command...      
 
Is it possible? why?
I don't have any idea , can u help me
I want to know the sql commands , if it's possible 

thanks for reply,
mochi

View Replies View Related

Delete Multiple Rows One At A Time Based On A Condition

 

Hi,
 
I have the following scenario :
CustomerDetail
customerid
customername
status
app_no
 
[status = 0 means customer virtually deleted]
 
CustomerArchive
archiveno             [autoincrement]
customerid
customername
status
 

At the end of the month, I have to physically delete customers. I have written two stored procs:
 
proc1
create proc spoc_startdeletion
as
declare @app_no int
select @app_no = (select app_no from customerdetail where status=0)
EXEC spoc_insertcustomerarchive @app_no
-- After transferrin, physically delete
delete from customerdetail where status=0
 
proc2
create proc spoc_insertcustomerarchive
@app_no int
as
insert into customerarchive(customerid,customername,status)
select customerid,customername,status from customerdetail where app_no = @app_no
 
It works fine if there is only one row with status=0, however the problem is that when there are multiple rows in customerdetail with status=0, it returns 'Subquery returned more than one value'
 
How can i transfer multiple rows one by one from the customerdetail to customerarchive and then delete the rows once they are transferred.
 
Vidkshi
 

View Replies View Related

SSE And Older Pc

I have an old Compaq Presario walmart pc. The stats on it are: 750MHz Duron, 512MB RAM, currently have 20GB and 30GB hard drives and planning on upgrading HD (100GB+), onboard nvidia vanta (8mb tnt2). If I get a larger hard drive, XP Pro, and put SQL Server Express on this computer, would I be able to get things done, or will this be extremely slowed down?

Thanks,
Nate

View Replies View Related

OLDER VALUES In DDL Trigger

Hi All
Can anyone help me to keep track of older view or stored Procedure that has been altered so that I can compare the two and make out the changes that have been made.At present I 'm able to get just the  altered view or storedProcedure but how to know that what has been changed.
This will be somewhat similar to Instead if and after in DML trigger but DDL triggers doesn't supports it.If anyone is aware of something then please help
THANKS in advance
Anisha
 

View Replies View Related

Deleting Older Files

I have a backup job that has failed.

The database size is 20.6 GB and the Transaction logs are 135MB

The amount of disk space I have left is 3.65MB. Which I know is not going to work.

However on the maintenance plan it is suppose to remove files older than 1 day.

I am wondering if a job works like this:

Step 1 create backup file
Step 2 Create Transaction Log Back up
Step 3 Delete old backup file
Step 4 Delete old Transaction log back up

Which tell me I would need to have double amount of disk space to accommodate 2 20 GB backup file and 2 135MB Transaction log file.

Is this correct??

Also is there a way that I can have step 3,4 done first.

Lystra

View Replies View Related

Comparing Dates (older Than)

Hi Guys,

I am really stuck on exactly how I can do this. I can sudo it in my mind but can't figure out how I would code it.

What i want to do is select all records from a table that the log_date is older than 7 days.

If I was comparing a date i would imagine i would do something like

log_date < NOW()-7

I have looked at:

SELECT player_name, player_last_logon_date,DATEDIFF(day, player_last_logon_date, GETDATE()) AS NumberOfDays from player

select player_name, player_last_logon_date, datepart(dd,player_last_logon_date) from player

but I dont think either is really right. any and all advice would be great!

Thanks

View Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved