mkdeluxe Posted October 27, 2005 Share Posted October 27, 2005 I'd like to delete records which are older than six month.I found a description, telling me to write the query like this:Try Dim Conn as System.Data.SqlClient.SqlConnection = NewSystem.Data.SqlClient.SqlConnection("data source=cen database=date;user id=...; pwd=...") Dim Comm as System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("DELETE * FROM Tb_Table WHERE MyDate < DATEADD(mm, -6, current_timestamp)") Conn.Open() Comm.ExecuteNonQuery()Catch console.WriteLine("mistake")End TryMy Application runs until Comm.ExecuteNonQuery() and then runs into the Catch-Statement. Can anybody find a mistake? Link to comment Share on other sites More sharing options...
Jerome Posted October 27, 2005 Share Posted October 27, 2005 I'm not sure but are you mixing an SQL query with asp code? Or in wich language are you writting? Link to comment Share on other sites More sharing options...
mkdeluxe Posted October 30, 2005 Author Share Posted October 30, 2005 This might be the case, I'm just beginning with programming. I use Visual Studio.Net, writing in VB. And my task is to connect to my database and delete every month those entries which are older than 6 month. I got the hint to do it like the way I posted before by a member of another forum. But this might be wrong, I have no idea...If you please could help me? Does SQL have those functions like DATEADD? Or how can I solve this task? Link to comment Share on other sites More sharing options...
maelstorm Posted October 30, 2005 Share Posted October 30, 2005 I'd like to delete records which are older than six month.I found a description, telling me to write the query like this:Try Dim Conn as System.Data.SqlClient.SqlConnection = NewSystem.Data.SqlClient.SqlConnection("data source=cen database=date;user id=...; pwd=...") Dim Comm as System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("DELETE * FROM Tb_Table WHERE MyDate < DATEADD(mm, -6, current_timestamp)") Conn.Open() Comm.ExecuteNonQuery()Catch console.WriteLine("mistake")End TryMy Application runs until Comm.ExecuteNonQuery() and then runs into the Catch-Statement. Can anybody find a mistake?<{POST_SNAPBACK}> If you want to delete the records that are 6 months old y not try the DATEDIFF command which is a lot easier. click below for the syntaxDatediff Link to comment Share on other sites More sharing options...
mkdeluxe Posted October 31, 2005 Author Share Posted October 31, 2005 Thank you for your help. Meanwhile I got the error in my code. My select statement was not right. It has to be...Dim Comm as System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("DELETE FROM Tb_Table WHERE MyDate < DATEADD(mm, -6, current_timestamp)")I only deletet the star in my query and suddenly it worked...Thanks and greetingsmkdeluxe Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now