How To Delete Records Based On Time?

Oct 8, 2003

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 6 Replies


ADVERTISEMENT

Delete Multiple Rows One At A Time Based On A Condition

Aug 28, 2007



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 15 Replies View Related

Delete Records Based On Derived Column Content

Nov 18, 2011

I have a MS SQL table with a derived column, for date the records were imported, and need to delete records, based on the content of this column. What I need to do is delete all records from the table with a date of '2011-11-18'. Now this column is a datetime column, so it contains the time info after the date, i.e. 2011-11-18 09:29:38.000, but no matter what command I try for this:

-Delete from table where Date_Imported like '2011-11-18%'
-Delete from table where Date_Imported like '2011-11-18'
-Delete from table where Date_Imported = 2011-11-18

It comes back saying "0 rows affected", even though I know there are records with that date in the table.

View 2 Replies View Related

Check 2 Tables A Delete Records Based On Common Fi

Aug 8, 2007

I'm kinda embarrased, it's been quite awhile since I've played with SQL or Access but here's my brain fart. I need to open an access DB by the name of eHomes. Inside there are two tables AD_IMAGE & PROPERTY. Property is the main table and stores most of the info. It has a field in it called AD_ID and others except for the image file name. The AD_IMAGE table just keeps the image file name and has 3 fields: ID - AD_ID - & Image1. The problem is when the ad is deleted from the property table the AD_IMAGE table record referring to the PROPERTY.AD_ID doen't get deleted. So, I just want the AD_IMAGE table cleaned up to match what's active in the PROPERTY table using the common AD_ID fields in both tables.

Thanks

View 1 Replies View Related

SQL Server 2012 :: Randomly Delete Records Based On Some Condition

Mar 19, 2014

create table #sample
(
Name varchar(100),
value int

[code]....

From that I wanted to delete some records based on following condition. randomly select any number of records but sum(value) = 125 and name = xxx

View 2 Replies View Related

Transact SQL :: Delete Query Based On Count Of Child Records?

Oct 12, 2015

I've been able to get this select query to work, but I'm not sure how to modify it to turn it into a DELETE query:
  
USE QSCTestENG
select p.[testid], COUNT(c.[testid])
FROM [dbo].[tblTestHeader] p
left outer join [dbo].[tblTestMeasurements] c ON p.[testid]=c.[testid]
where p.[model] = 'XPPowerCLC125US12'
group by p.[testid]
having COUNT(c.[testid]) <>48;

View 2 Replies View Related

Find Phonecall Records Based On User At The Time Of Call

Oct 8, 2006

i have a table of phonecall records detailing amongst other things the time and duration of the call. there is a relationship linking this table to a table of handsets and from there to a table of users. this users table lists the start date the user had the phone. as ever sample data might make this easier to show:

PhoneUsers table

PhoneUserID Name StartDate PhoneID

1 john 1/08/2006 1

2 bob 20/08/2006 1

3 fred 2/08/2006 2

etc

Phones table:

PhoneID

1

2

etc

PhoneCalls table:

PhoneCallID PhoneID CallDate Duration

1 1 10/08/2006 25

2 1 23/082006 20

3 2 23/08/2006 20

i want the following result set:

user calldate duration

john 10/08/2006 25

bob 23/08/2006 20

fred 23/08/2006 20

essentially i am trying to link the phonecalls to the user of the phone at the time of the call. i make the following assumptions regarding the data:

the user is responsible for all calls for a given handset from the 'startdate' until the next 'startdate' for a different user, if such a record exists, if not then for all calls after that date



thanks



View 1 Replies View Related

How To Convert UTC Time (retrieved From SQL) To Local Time In Reporting Services Based On Time Zone

Aug 7, 2007



Hi all,

I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.

I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.

For example if the date is August 07, 2007 10:00 AM UTC,

then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.


Similarly for other Time Zones it should display the time accordingly.

Is this possible in SSRS 2005?

Any pointers will be usefull...

Thanks in advance
sudheer racha.

View 5 Replies View Related

SQL 2012 :: Query To Make Single Records From Multiple Records Based On Different Fields Of Different Records?

Mar 20, 2014

writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.

ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29

output should be ......

ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29

View 0 Replies View Related

SQL Server 2008 :: Loop Through Date Time Records To Find A Match From Multiple Other Date Time Records?

Aug 5, 2015

I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.

E.g.

Query 1

Select ID, Person, ProposedEvent, DayField, TimeField
from MyOptions
where person = 'me'

Table

Select Person, ExistingEvent, DayField, TimeField
from MyTimetable
where person ='me'

Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.

I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.

View 5 Replies View Related

Master Data Services :: Hard Delete All Soft Delete Records (members) In Database

May 19, 2012

I am using Master Data Service for couple of months now. I can load, update, merge and soft delete data in MDS. Occasionally we even have to hard delete data from MDS. If we keep on soft deleting records in a MDS table eventually there will be huge number of soft deleted records. Is there an easy way to hard delete all the soft deleted records from all MDS tables in a specific Model.

View 18 Replies View Related

Data Flow Task To Delete Records And Then Insert Records In Transaction

Aug 6, 2007

HI,

I have been trying to solve the locking problem from past couple of days. Please help mee!!

Scenario:
--------------
I have a SSIS package in which 2 data flow tasks. 1st data flow task deletes records from a 5 tables and the 2nd data flow task should insert records into 1 of the five tables after the success of 1st data flow task. This scenario runs in Transacation.

The above scenrio in the 2nd data flow task hangs in runtime. It does not complete. with sp_who2 command i could see that there is an intent share lock(LK_M_IS) on the table and the status is SUSPENDED.

I dont know how to come out of this locking. Please help.

Thanks ,
Sunil

View 7 Replies View Related

SQL 2012 :: Accurate Sorting Data Each Time With Millions Of Records Without Time Field?

Apr 25, 2014

Sample Table

USE [Testing]
GO
/****** Object: Table [dbo].[Testing] Script Date: 4/25/2014 11:08:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[Code] ....

It seems to work fine with one million records.

Each primary key is unique, but the begindate is non-unique, and i guess even if i use datetime2 and add nanoseconds, from what i have read, there is a chance that i could have a duplicate datetime since the date is imported via XML from multiple sources.

View 7 Replies View Related

Delete Table Based On 2 Parameter

Jun 24, 2014

I am getting runtime error for the below simple execution of sp even I tried casting the @dt to varchar still getting the same error. I want to delete the table based on the 2 parameter ...

1. Table Name first parameter
2. InCondition is the column name which of type datetime

CREATE PROCEDURE dbo.[DeleteTable](@InTblName NVARCHAR(250),@InCondition NVARCHAR(250))
AS
BEGIN
DECLARE @DeleteSQL NVARCHAR(250)
DECLARE @Dt DATETIME
SET @Dt = GETDATE()
SELECT @DeleteSQL = N'DELETE FROM ' + @InTblName +' WHERE '+@InCondition+ '=''' + @Dt+ ''''
SELECT(@DeleteSQL)
EXECUTE sp_executesql @DeleteSQL
END

I have corrected the code now, I am not getting the output it is throwing error at run time.

EXEC [DeleteTable] 'TABLE_NAME','COLUMN_NAME'

Where column_name is of datatype datetime

Msg 241, Level 16, State 1, Procedure spDeleteTable, Line 8
Conversion failed when converting date and/or time from character string.

View 12 Replies View Related

Delete From Table Based On Getdate

Oct 29, 2007

Greetings!

Need some assistance to:

Delete records from a table; keying off of a colum (expire date) thats data-type is datetime.

I would like to use command getdate less 2 years. So - I want to delete all records from a table where the expire date is 2 years older than the current date.

Appreciate the help!

roscoeLL9

View 4 Replies View Related

Delete File Based On Criteria

Jul 10, 2006

Hi,

I want to delete all files in a folder where creation date is an year older. I created a For Each Loop container to go over the whole folder and then execute the following Script Task

Dim FileArchive As Int16

Dim dteArchive As Date

Dim FilePath As String

FileArchive = CType(Dts.Variables("FileArchive").Value, Int16) ' One Year FileArchive = 365

dteArchive = DateAdd("d", -FileArchive, Now.Date)

FilePath = Dts.Variables("FilePath").Value.ToString

If File.GetCreationTime(FilePath) <= dteArchive Then

Try

File.Delete(FilePath)

Catch ex As Exception

'Do Nothing

End Try

End If

Dts.TaskResult = Dts.Results.Success

Is there a better approach to this? As most of the time job may have to delete 10 or 15 files but he has to read each file and then check the date if it matches the criteria and it delete.

View 1 Replies View Related

T-SQL (SS2K8) :: Delete Duplicates From Table Based On Two Columns?

May 20, 2015

Assuming I have a table similar to the following:

Auto_ID Account_ID Account_Name Account_Contact Priority
1 3453463 Tire Co Doug 1
2 4363763 Computers Inc Sam 1
3 7857433 Safety First Heather 1
4 2326743 Car Dept Clark 1
5 2342567 Sales Force Amy 1
6 4363763 Computers Inc Jamie 2
7 2326743 Car Dept Jenn 2

I'm trying to delete all duplicate Account_IDs, but only for the highest priority (in this case it would be the lowest number).

I know the following would delete duplicate Account_IDs:

DELETE FROM staging_account
WHERE auto_id NOT IN
(SELECT MAX(auto_id)
FROM staging_account
GROUP BY account_id)

The problem is this doesn't take into account the priority; in the above example I would want to keep auto_ids 2 and 4 because they have a higher priority (1) than auto_ids 6 and 7 (priority 2).

How can I take priority into account and still remove duplicates in this scenario?

View 3 Replies View Related

How To Delete Existing Data Based On Where Clause Condition

Feb 18, 2014

I have written a merge Statement where i am facing trouble to delete the data basing on Where Clause Condition.

1) Case 1 : For example i have inserted Data from Source to Target based on Date Key Condition.Take an Instance 10 Records Inserted.
2) Case 2 : For example some changes in the records and it has been updated through the Merge Statement .
3) Case 3 : For the Same Date key based on Conditions now three records has came and need to be inserted and rest should be deleted for that Date Key.

How i need to proceed on this before 10 records are not getting deleted and new records adding for that one

My Example Code :

MERGE INTO TargetTable AS Target
USING (
SELECT DISTINCT Col1,
Col2
FROM Table1 AS cga
)
ON dad.AnchorDate = CASE

[Code] ....

View 1 Replies View Related

Filtering Out 1 Time Dimension Based On The Selection In Another Time Dimension

May 19, 2008

Hi!

Need some help building a query that does the following :

I have 2 Time Dimensions ; Time (Transdate) and ClosedDate (ClosedDate)

In my report/query, if [Time].CurrentMember = [Time].[YMD].[YMD].[2006].[200610].[20061031] I want to FILTER out all ClosedDate < [ClosedDate].[YMD].[YMD].[2006].[200610].[20061031]

Both Time Dimensions are Year -> Month -> Day and have the same Members.

I have every option available, using calculated Members and/or Measures to do this.

The report I'm creating is Aging of Receivables : Balance / 30 days / 60 days / etc.. But for the Aging, I need to filter like explained above.

Appreciate all help!

Regards,
Stian Bakke

View 3 Replies View Related

T-SQL (SS2K8) :: Dynamically Delete Data Based On Date Column

May 19, 2015

DELETE FROM Report_temp2
WHERE MSSalesID in
( Select Report_temp.MSSalesID FROM Report_temp)

DELETE FROM Report_temp
WHEREMSDate < '2015-07-01'

Actually the year stating form july.

Q1 is july,aug,sep.
Q2 is oct nov,dec.
Q3 is jan,feb,mar.
Q4 is april, may,june.

So what I need is dynamically I want to delete the data every year prior to current year.

View 4 Replies View Related

Add At The Same Time Delete

Jan 18, 2008

[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 1 Replies View Related

Add At The Same Time Delete

Jan 20, 2008

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 11 Replies View Related

Delete Records ?

Jan 31, 2007

Hello, its hard to explain, i have a table like this: --userpage_visitors-- id bigint owner nvarchar(20) visitor nvarchar(20) created datetime
Then i have some code like this:  (@Visitor is send to the stored proc)DECLARE @lastuser nvarchar(20)SELECT TOP 1 @lastuser = visitor FROM userpage_visitors WHERE (owner = @UserName) ORDER BY created DESCIF (@lastuser <> @Visitor)BEGININSERT INTO userpage_visitors (owner, visitor, created) VALUES (@UserName, @Visitor, @Created)-- delete hereEND
Now after i have inserted the new visitor into the table, i need to clean the table... so each user should have maximum of 30 visitors, so if the user i inserted above is the 31st user then i need to delete the first user, so i always have 30 fresch visitors,, if they have less then 30 visitors then nothing should happen. The question is, how can i get the 31th post? in mysql you can say that you want post 30, 31, but in mssql you only have the TOP to select limited posts, any ideas?
Patrick

View 1 Replies View Related

DELETE Records.

Mar 21, 2007

Hello, I have 3 tables with their columns as follows:   + LabelsInDocs [LabelId] PK FK , [DocsId] PK FK   + Labels [LabelId] PK , [LabelName]   + Docs [DocId] PK , [DocUrl] I set Cascade Delete On so when I delete a Doc all records in LabelsInDocs will be deleted. However, when a Doc is deleted I want also to delete all records in Labels for the labels which do not have any Doc associated to it in LabelsInDocs. How can I do this? Thanks, Miguel

View 1 Replies View Related

Delete Records ???

Jun 3, 2008

hello friends.
i have table1 in aspnetdb.mdf and i have picturefile of columname.. datatype of picturefile is vchar(50)
i want to delete my record automatically from my table1 after two weeks from inserting my record date..
i want to delete my picture file that located /pictures/tree.jpg (example).. pictures/tree.jpg was uploaded as picturefile on table1 by user before
how can i do this ?
cheers

View 5 Replies View Related

Pls Help!!! Delete Records In VB.net

Nov 29, 2003

Currently i writing a program to delete Records in the SQL database using VB.net language...
BUT i not sure whether i am right???
Pls provide me with the coding using a command object to delete records in SQL database...thank...however i tried the codes below but not working ...Pls help

Dim StrConnection As String = "workstation id=""ET-T15404-PC1"";integrated security=SSPI; etc

Dim objConnection As New SqlConnection(strConnection)
Dim strSQL As String = "Delete Seller.Admin FROMSeller WHERE Seller.no=tb.no"
Dim dbComm As New SqlCommand(strSQL, objConnection)

objConnection.Open()
dbComm.ExecuteNonQuery
objConnecion.Close()

View 1 Replies View Related

Delete Records

Jun 29, 2005

Hi.

there are 300 records on the table.
the table has a field of "ID (autonumber)"

How can I delete records without latest 20 records?

View 2 Replies View Related

Delete Records

Oct 9, 2006

There are 30 tables in my database and I need to delete all records within a date range. Could anyone help me?

View 4 Replies View Related

Delete Records

Sep 20, 2005

Hello,I have a database called articlesI want to delete all articles from a subgroup e.g. DVDIf I am in the enterprisemanager what steps do I have to take to do thisthanks in advancePaul

View 5 Replies View Related

Records Won't Delete

Jul 20, 2005

I have some records that will not delete, whenever I run a deletestatement in the Query analyzer, it never completes the statement, andI am only deleting one record at a time. Can anyone tell me why arecord wouldn't delete?

View 1 Replies View Related

Delete Records Every Day

Nov 8, 2006

Hi!

New on this

Is there a way on a sql sever where i can
delete som records one time every day
like if a field in my table is < getdate()

Alvin



View 6 Replies View Related

Time Based Trigger

Feb 16, 2005

can i make a trigger to fire based on given date and time?

View 2 Replies View Related

Filtering Based On Time Of Day

Feb 14, 2005

While using a DateTime field, is there an easy way of filtering based on time of day? Ex. Anything that happened after 3:00 PM on any given day?

The easiest way I've found of doing so is :

Code:


SELECT *
FROM MyTable
WHERE ({ fn MOD(DATEDIFF(ss, '01/01/2000 00:00:00', [Date]), 3600 * 24) } >= DATEDIFF(ss, '00:00:00', '15:00:00'))



To me that seems a bit complicated... but it works...

Anyone that knows of a better way any help would be appreciated (or if you don't think there's a better way knowing that would help as well)

-MBirchmeier

View 5 Replies View Related







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