DELETE FROM dbo.DateTimeCRUDTest
WHERE MONTH(InsertDate) = MONTH(GETDATE())-1 AND YEAR(InsertDate) = YEAR(GETDATE())

#You need SQLPSX to run this.
$MyNumber = 100000

for($i=0; $i -lt $MyNumber; $i++)
{
	$MyDate = ([datetime]::now).AddDays(-$i) -f "YYYYMMDD"
	Set-SqlData -dbname TestDb -sqlserver "(local)" -qry "INSERT INTO DateTimeCRUDTest(Description,InsertDate) VALUES('Some text to add a little realism - $MyDate', '$MyDate')" 
}

USE TestDb
GO

DELETE FROM dbo.DateTimeCRUDTest
WHERE InsertDate BETWEEN DATEADD(MONTH,datediff(month,0,getdate())-1,0) AND DATEADD(day,-1,DATEadd(MONTH,datediff(month,0,GETDATE()),0))
--RESTORE DB
USE TestDb
GO

DELETE FROM dbo.DateTimeCRUDTest
WHERE MONTH(InsertDate) = MONTH(GETDATE())-1 AND YEAR(InsertDate) = YEAR(GETDATE())

USE TestDb
GO

DELETE FROM dbo.DateTimeCRUDTest
WHERE InsertDate BETWEEN DATEADD(MONTH,datediff(month,0,DATEADD(YEAR,-100,getdate()))-1,0) AND DATEADD(day,-1,DATEadd(MONTH,datediff(month,0,DATEADD(YEAR,-100,getdate())),0))


USE TestDb
GO

DELETE FROM dbo.DateTimeCRUDTest
WHERE MONTH(InsertDate) = MONTH(GETDATE())-1 AND YEAR(InsertDate) = YEAR(GETDATE())-100

quote:

---

Originally posted by namman



.0431 seconds and .0415 seconds respectively. Not a huge change. I added an index to the datetime column and got .037 and .026 respectively. Not a big variation, but the "wrong query" was running faster.


Because both are "wrong" queries

---