sql delete statement where date is greater than 30 days

Question

I need a SQL statement to delete row that are older than 30 days  My table events has a field date that contains the date and the time it was inserted in the database  Will this work  SELECT   from Results WHERE  Date   gt   DATEADD d  -30  getdate

User · Answer

Instead of converting to varchar to get just the day  convert varchar 8    Date   112    I prefer keeping it a datetime field and making it only the date  without the time    SELECT   FROM Results  WHERE CONVERT date   Date    gt   CONVERT date  GETDATE

User · Answer

You could also set between two dates   Delete From tblAudit WHERE Date dat  lt  DATEADD day  -360  GETDATE    GO Delete From tblAudit WHERE Date dat  gt  DATEADD day  -60  GETDATE    GO

User · Answer

Use DATEADD in your WHERE clause       WHERE date  lt  DATEADD day  -30  GETDATE      You can also use abbreviation d or dd instead of day

User · Answer

You could also use  SELECT   from Results WHERE date  lt  NOW   - INTERVAL 30 DAY

User · Answer

Although the DATEADD is probably the most transparrent way of doing this  it is worth noting that simply getdate  -30 will also suffice   Also  are you looking for 30 days from now  i e  including hours  minutes  seconds  etc  Or 30 days from midnight today  e g  12 06 2010 00 00 00 000   In which case  you might consider   SELECT    FROM Results  WHERE convert varchar 8    Date   112   gt   convert varchar 8   getdate    112

User · Answer

To delete records from a table that have a datetime value in Date column older than 30 days use this query   USE Database name  DELETE FROM Table name WHERE Date column  lt  GETDATE   - 30      or this   USE Database name  DELETE FROM Table name WHERE Date column  lt  DATEADD dd -30 GETDATE      To delete records from a table that have a datetime value in Date column older than 12 hours   USE Database name  DELETE FROM Table name WHERE Date column  lt  DATEADD hh -12 GETDATE      To delete records from a table that have a datetime value in Date column older than 15 minutes   USE Database name  DELETE FROM Table name WHERE Date column  lt  DATEADD mi -15 GETDATE      From  http   zarez net  p 542

User · Answer

We can use this       DELETE FROM table name WHERE date column  lt              CAST CONVERT char 8    DATEADD day -30 GETDATE      112  AS datetime    But a better option is to use   DELETE FROM table name WHERE DATEDIFF dd  date column  GETDATE     gt  30   The former is not sargable  i e  functions on the right side of the expression so it can   t use index  and takes 30 seconds  the latter is sargable and takes less than a second

User · Answer

GETDATE   didn t work for me using mySQL 8      ERROR 1305  42000   FUNCTION mydatabase GETDATE does not exist   but this does   DELETE FROM table name WHERE date column  lt  CURRENT DATE - 30

[sql-server] sql delete statement where date is greater than 30 days

Examples related to sql-server

Examples related to date-arithmetic