SQL Server Performance

Delete records from database based on condition

Discussion in 'SQL Server 2005 General Developer Questions' started by Jeena, Oct 17, 2007.

  1. Jeena New Member

    Hi all,
    I want to delete some records which i inserted 5 days before.Hw to calculate the difference between two dates and delete the appropriate records.
    Pls help me.

    Thanks Jeen
    Jeena, Oct 17, 2007
    #2

  2. FrankKalis Moderator

    DELETE <table>
    WHERE <your data column> = DATEADD(DAY, -5, '20071017')
    FrankKalis, Oct 17, 2007
    #3

  3. Jeena New Member

    Thanks for ur solution..
    But this process should be done everday. So we cant enter a specific date. Im having some date values in the database.It should be compared with the current date and then delete.Actually im doing this based on a website project.So this query is needed for that
    Jeena, Oct 17, 2007
    #4

  4. Madhivanan Moderator

    [quote user="Jeena"]
    Thanks for ur solution..
    But this process should be done everday. So we cant enter a specific date. Im having some date values in the database.It should be compared with the current date and then delete.Actually im doing this based on a website project.So this query is needed for that
    [/quote]
    Read my previous reply
    Madhivanan, Oct 17, 2007
    #5

  5. Madhivanan Moderator

    [quote user="FrankKalis"]
    DELETE <table>
    WHERE <your data column> = DATEADD(DAY, -5, '20071017')
    [/quote]
    Why dont you use Getdate() instead of coding the date? [;)]
    Madhivanan, Oct 17, 2007
    #6

  6. Jeena New Member

    Actually my problem is that:
    I hav a table that contains a column Sdate. Values r entered from forms itself(.Net).The requirement is that i want to delete the records that r entered 5 days back. Today if i entered 5 records to database, that records should be deleted after 5 days. This is based on a website project in VB.Net. So im using SQl server for this purpose.
    Jeena, Oct 17, 2007
    #7

  7. FrankKalis Moderator

    [quote user="Jeena"]
    Actually my problem is that:
    I hav a table that contains a column Sdate. Values r entered from forms itself(.Net).The requirement is that i want to delete the records that r entered 5 days back. Today if i entered 5 records to database, that records should be deleted after 5 days. This is based on a website project in VB.Net. So im using SQl server for this purpose.
    [/quote]
    Create a stored procedure with the mentioned statement and schedule it to run once a day. That should be fine.
    FrankKalis, Oct 17, 2007
    #8

  8. Jeena New Member

    In Vb.Net coding itself i wil write the query to delete the records that r 5days old. It wil be in the main page itself. So that the users can view the details correctly.This table is for storing the advertisments.
    thanks
    Jeena
    Jeena, Oct 17, 2007
    #9

  9. MichaelB Member

    Create a SP, then call the SP from the page. Am I missing something?
    MichaelB, Oct 17, 2007
    #10

  10. Jeena New Member

    Thank You for all the solutions. It helped.
    Jeena
    Jeena, Oct 17, 2007
    #11

  11. MichaelB Member

    Glad to hear it. Please stay in the community. It is a great place and you will learn fast!
    MichaelB, Oct 17, 2007
    #12

  12. satya Moderator

    Or even you can schedule this as a SQLAgent job to perform the action on specific timings.
    satya, Apr 19, 2010
    #13

  13. ShawnG21 New Member

    I understand how to schedule it but the command doesn't work.
    ShawnG21, Apr 19, 2010
    #14

  14. Adriaan New Member

    If the SDate is entered from the user interface, you'll just have to trust that it's correct. And you might have people entering data from all over the world, and some user may still be on the previous calender day.
    If there is a hidden date column with a default of GETDATE(), then at least you can trust the clock of the computer hosting SQL Server.
    Adriaan, Oct 17, 2007
    #15

  15. FrankKalis Moderator

    <P mce_keep="true">[quote user="Madhivanan"] <P>[quote user="FrankKalis"] <P>DELETE &lt;table&gt;<BR>WHERE &lt;your data column&gt; = DATEADD(DAY, -5, '20071017')</P><P>[/quote]</P><P>Why dont you use Getdate() instead of coding the date? <IMG alt=Wink src="http://sql-server-performance.com/Community/emoticons/emotion-5.gif"></P><P>[/quote]</P><P>Don't know. I guess I have just forgotten.</P>
    FrankKalis, Oct 17, 2007
    #16

  16. Madhivanan Moderator

    [quote user="Jeena"]
    Hi all,
    I want to delete some records which i inserted 5 days before.Hw to calculate the difference between two dates and delete the appropriate records.
    Pls help me.

    Thanks Jeen
    [/quote]
    Before trying this query take a table backup
    1 Delete from table where datediff(day,datecol,getdate())=5
    2 Delete from table where datecol>=dateadd(day,datediff(day,0,getdate()),-5) and datecol<dateadd(day,datediff(day,0,getdate()),-4)
    Madhivanan, Oct 17, 2007
    #17

  17. Adriaan New Member

    First of all, you need to know that you cannot delete rows (records) from a database, only from a table. Second, your table must have a column (field) where the date of insertion is recorded, because the database does not keep track of this type of detail.
    Assuming you have a table dbo.MyTable with an InsertDate column, then you could use:
    DELETE FROM dbo.MyTable T
    WHERE DATEDIFF(DAY, T.InsertDate, GETDATE()) = 5
    Run the following query before the delete query, to make sure you're deleting the correct rows.
    SELECT T.* FROM dbo.MyTable T
    WHERE DATEDIFF(DAY, T.InsertDate, GETDATE()) = 5
    Adriaan, Oct 17, 2007
    #18

  18. ShawnG21 New Member

    I am trying to delete data that is 14 months old so I am trying to modify your command but it isn't working correctly. If I do the select anything that would be in the current year, it works, but if it goes to the previous year, then it doesn't pull back any results. Here is the command that I am trying to use. Thanks!!SELECT
    * FROM dbo.Table WHERE DATEDIFF
    (MONTH, Date_Column, GETDATE()) = 14
    ShawnG21, Apr 14, 2010
    #19

Share This Page