Delete records from the database using Date()

Discussion in 'Databases' started by akinak, Feb 5, 2009.

0
  1. #1
    Can anyone give me a SQL command to delete all records from the databse where Date = today-1?

    Thanks
     
    akinak, Feb 5, 2009 IP
  2. crivion

    crivion Notable Member

    Messages:
    1,669
    Likes Received:
    45
    Best Answers:
    0
    Trophy Points:
    210
    Digital Goods:
    3
    #2
    which format are you using for date into db? its unix timestamp like a number 123454 or just 12.12.2012?
    do this php script
    <?php
    //for int timestamp
    $yesterday = strtotime("- 1 day");
    $sql = mysql_query("delete from tableName where Date = '$yesterday'");
    //or for simple date
    $yesterday = strtotime("- 1 day");
    $yesterday = date("d.m.Y");
    $sql = mysql_query("delete from tableName where Date = '$yesterday'");
    ?>
     
    crivion, Feb 6, 2009 IP
  3. akinak

    akinak Peon

    Messages:
    256
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    thnx crivion..but can you please rewrite the code in ASP?
     
    akinak, Feb 6, 2009 IP
  4. crivion

    crivion Notable Member

    Messages:
    1,669
    Likes Received:
    45
    Best Answers:
    0
    Trophy Points:
    210
    Digital Goods:
    3
    #4
    ahh, nope sorry I dont know asp
     
    crivion, Feb 7, 2009 IP
  5. chisara

    chisara Peon

    Messages:
    141
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Just use the queries in the code crivion gave and then replace $yesterday with something like Interval
    Assuming a Date column this should help you on your way, since this is the Database forum and I don't do ASP I can't help you to code it.
    DELETE FROM tableName WHERE Date = now()- INTERVAL'1 Day';
     
    chisara, Feb 7, 2009 IP
  6. akinak

    akinak Peon

    Messages:
    256
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #6
    thnx chisara...I got what youare trying to say here..but I still don't know how to set the interval.

    How would you do in MS Access databse using SQL queries?
     
    akinak, Feb 8, 2009 IP
  7. chisara

    chisara Peon

    Messages:
    141
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #7
    The idea is to have the current date minus the interval of one day, I am not that familiar with the MS-Access implementation, so I can only give you the specific direction but I can't help you with an answer.

    P.S. My Answer was an SQL query for Postgresql, MS-Access uses nearly the same SQL language.
     
    chisara, Feb 8, 2009 IP