Remember that the SQL data type has hours, minutes, seconds and sometimes milliseconds also, so you must account for that if needed.

To print todays date including the time SELECT getdate()
To print yesterdays date including the time SELECT dateadd(dd, -1, getdate())
To print date of 2 months ago including the time SELECT dateadd(mm, -2, getdate())
To print last years date including the time SELECT dateadd(yy, -1, getdate())

To print todays date but set time to 00:00:00.000 SELECT dateadd(dd, 0, datediff(dd,0, getdate()))
To print yesterdays date but set time to 00:00:00.000 SELECT dateadd(dd, -1, datediff(dd,0, getdate()))

or
To reset hours to 00:00:00 you can also use CONVERT(VARCHAR, datedate(),7) note this will display like 'May 21, 11' but when used in a WHERE date > Convert... will ignore hours correctly

So to select every records in a table for a static date we could do it the hard way with:
SELECT * FROM sometable WHERE somedate BETWEEN '2010-06-01 00:00:00.00' AND '2010-06-01 23:59:59.999'

Or we could just convert the somedate column to NO time first (the easy way)
SELECT * FROM sometable WHERE dateadd(dd, 0, datediff(dd, 0, somedate)) = '2010-06-01' or = '2010-06-01 00:00:00.00' it assumes 00's

If we wanted a rolling query, to pull every records from yesterday:
SELECT * FROM sometable WHERE dateadd(dd, 0, datediff(dd, 0, somedate)) = dateadd(dd, -1, datediff(dd,0, getdate()))

To get the first day of this month, with current time SELECT DATEADD(dd, -(DAY(getdate())-1), getdate())
To get the first day of this month, with time set to 00 SELECT DATEADD(dd, -(DAY(getdate())-1), DATEADD(dd, 0, DATEDIFF(dd, 0, getdate())))

To get the first day of last month, with current time SELECT DATEADD(dd, -(DAY(getdate())-1), DATEADD(mm, -1, getdate()))
To get the first day of last month, with time set to 00 SELECT DATEADD(dd, -(DAY(getdate())-1), DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(mm, -1, getdate()))))

To get the end of this month, with current time SELECT DATEADD(dd, -DAY(getdate()), DATEADD(mm, 1, getdate()))
To get the end of this month, with time set to 00 SELECT DATEADD(dd, -DAY(getdate()), DATEADD(mm, 1, DATEADD(dd, 0, DATEDIFF(dd, 0, getdate()))))
To get the end of this month, with time set to 23:59:59:997 SELECT DATEADD(dd, -DAY(getdate()-1), DATEADD(mm, 1, DATEADD(ms, -2, DATEADD(dd, 0, DATEDIFF(dd, 0, getdate())))))

To get the end of last month, with time set to 23:59:59:997 SELECT DATEADD(dd, -DAY(getdate()-1), DATEADD(mm, 1, DATEADD(ms, -2, DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(mm, -1, getdate()))))))

To select every records for last month:

DECLARE @last_month_start datetime, @last_month_end datetime
SET @last_month_start = DATEADD(dd, -(DAY(getdate())-1), DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(mm, -1, getdate()))))
SET @last_month_end = DATEADD(dd, -DAY(getdate()-1), DATEADD(mm, 1, DATEADD(ms, -2, DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(mm, -1, getdate()))))))
SELECT * FROM sometable WHERE somedate BETWEEN @last_month_start AND @last_month_end
 

To find last week
http://www.objectreference.net/post/SQL-Find-last-week-date-range.aspx

DECLARE
    @now AS datetime,
    @todayDayOfWeek AS INT,
    @startOfPrevWeek AS datetime,
    @endOfPrevWeek AS datetime
 
SET @now = getdate()
--SET @now = '2011-05-26'
 
--get number of a current day (1-Monday, 2-Tuesday... 7-Sunday)
SET @todayDayOfWeek = datepart(dw, @now)
 
--get this last Sunday (hours will be 00:00:00)
SET @endOfPrevWeek = DATEADD(dd, -@todayDayOfWeek+1, @now)
SET @endOfPrevWeek = DATEADD(dd, 0, DATEDIFF(dd,0, @endOfPrevWeek))
 
--get this Sunday before last
SET @startOfPrevWeek = DATEADD(dd, -7, @endOfPrevWeek)
 
--Example query for entire last week (Sunday 00:00:00 - Saturday 23:59:59)
--Note @endOfPrevWeek is actually Sunday 00:00:00 so if we use < then we actually get Saturday 23:59:59
--SELECT * WHERE datecolumn >= @startOfPrevWeek AND datecolumn < @endOfPrevWeek
 

Date fields are usually yyyy-mm-dd hh:mm:ss.mil
To retrieve everything for today you could do a >= yyyy-mm-dd 00:00:00 and <= yyyy-mm-dd 23:59:59 (or use between)
But sometimes you need to ignore the hh:mm:ss.mil all together

--dateadd(dd,0, datediff(dd, 0, dateField))

SELECT * FROM viewTrkTrackers WHERE
    dateadd(dd,0, datediff(dd,0,closeDate)) = '04-30-2008'

This will show everything 10 days back, regardless of time

SELECT * FROM viewTrkTrackers WHERE
    (dateadd(dd,0, datediff(dd,0,closeDate)) >= (dateadd(dd,0, datediff(dd,0, (dateadd(dd, -10, getdate()))))))

Return as nvarchar of mm/dd/yyyy

DECLARE @startDate as nvarchar(10)
--@startDate = the last day of last month (if today is 3/7 or 3/25... @startDate will be 2/29)...
SET @startDate = CONVERT(nvarchar(10), DATEADD(dd, -1, CONVERT(nvarchar(10), CONVERT(nvarchar(2), DATEPART(mm, @nowDate)) + '/01/' + CONVERT(nvarchar(4), DATEPART(yy, @nowDate)))), 101)

Return is nvarchar of mm/dd/yyyy

SELECT CONVERT(nvarchar(10), CONVERT(nvarchar(10), CONVERT(nvarchar(2), DATEPART(mm, GETDATE())) + '/01/' + CONVERT(nvarchar(4), DATEPART(yy, GETDATE()))), 101)

DECLARE 
    @startDate as smalldatetime,
    @endDate as smalldatetime,
    @startThisMonth as nvarchar(10),
    @startLastMonth as nvarchar(10),
    @endLastMonth as nvarchar(10)

SET @startThisMonth = CONVERT(nvarchar(10), CONVERT(nvarchar(10), CONVERT(nvarchar(2), DATEPART(mm, GETDATE())) + '/01/' + CONVERT(nvarchar(4), DATEPART(yy, GETDATE()))), 101)
SET @startLastMonth = CONVERT(nvarchar(10), CONVERT(nvarchar(10), CONVERT(nvarchar(2), DATEPART(mm, DATEADD(mm, -1, GETDATE()))) + '/01/' + CONVERT(nvarchar(4), DATEPART(yy, GETDATE()))), 101)
SET @endLastMonth = CONVERT(nvarchar(10), DATEADD(dd, -1, CONVERT(nvarchar(10), CONVERT(nvarchar(2), DATEPART(mm, GETDATE())) + '/01/' + CONVERT(nvarchar(4), DATEPART(yy, GETDATE())))), 101)

--Add startDate/endDate logic here later

SET @startDate = CONVERT(smalldatetime, @startLastMonth) + '00:00:00'
SET @endDate = CONVERT(smalldatetime, @endLastMonth) + '23:59:59'
SELECT @startDate
SELECT @endDate

--So use start >= @startDate and end < @endDate

produces
2008-04-01 00:00:00
2008-05-01 00:00:00