Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Oct 2011
    Posts
    85
    Thanks
    13
    Thanked 1 Time in 1 Post

    sql datetime compare

    I can't figure out how to compare a datetime field in my sql database with the current dateTime. I need to delete the old ones, say 2 hours old.

    I've tried
    Code:
    SqlConnection conn = "yada yada";
    
    SqlCommand cmd = new SqlCommand("DELETE FROM chatData Where chatTime < " + System.DateTime.Now , conn);
    
                try {
                    conn.Open();
                    
                    int rows = cmd.ExecuteNonQuery();
    
                    conn.Close();
    
                    return "worked";
                } catch (Exception e) { return e.Message; }
    this just throws a syntax error
    Last edited by footballer27; 05-25-2012 at 01:57 AM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,536
    Thanks
    80
    Thanked 4,490 Times in 4,454 Posts
    There is no such thing as a "sql database". SQL is a language.

    I am *ASSUMING* you mean "SQL Server database," yes?

    So...you do it all in SQL:
    Code:
    SqlCommand cmd = new SqlCommand(
        "DELETE FROM chatData Where chatTime < DATEADD(h,-2,getDate()",
        conn
        );
    Despite its name, getDate() actually returns the current date AND time.

    So you subtract 2 hours from that, by passing -2 to DATEADD, and voila!
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    footballer27 (05-25-2012)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •