SELECT DATEADD(d, DATEDIFF(d,0,GETDATE()), 0) A
INTO #2
FROM Util..Tally

SELECT CONVERT(DATETIME,DATEDIFF(dd,0,GETDATE())) A
INTO #1
FROM Util..Tally

DROP TABLE #1
DROP TABLE #2

(200000 row(s) affected)

 SQL Server Execution Times:
   CPU time = 78 ms,  elapsed time = 95 ms.

(200000 row(s) affected)

 SQL Server Execution Times:
   CPU time = 62 ms,  elapsed time = 93 ms.

DECLARE @dtDate datetime
SET @dtDate = datediff(d,0,getdate())
PRINT @dtDate

SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 2) 
--: 2010-03-01 00:00:00.000 Start of the day 2 days from now
SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -2)
--: 2010-02-25 00:00:00.000 Start of the day 2 days ago.

SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -1)