Get the date range for the current month?

Question

I would like to create a SQL statement to later be used in my code, that gets the date range for the current month.

Example: This is August, so the date range would be

StartDate = 08/01/11
EndDate = 08/31/11

however, if it was February

StartDate = 02/01/11
EndDate = 02/28/11

Select * 
from mytable 
where (check_date >= StartDate) AND (check_date <= EndDate)

thanks for any help you may be able to give

Answer 1

The you can find the start of this month with the months-since-zero trick. The last day of the month is one month later, minus one day:

select  dateadd(month,datediff(month,0,getdate()),0)
,       dateadd(day,-1,dateadd(month,datediff(month,-1,getdate()),0))

This prints:

1-aug-2011    31-aug-2011

Answer 2

I needed very similar thing, to get month's date range from specific date, which was possible to use in SQL search - that means not only dates must be correct, but time must be from 00:00:00 to 23:59:59 if in 24 hour system, but it just matter of displaying DATETIME format, it will work with 12 hours system too.

Maybe it will be useful for someone. This solution is based on Andomar's answer:

-- Parameter date, which must be given to this code
DECLARE @date DATETIME
SET @date = GETDATE() -- for testing purposes initializing some date

-- Declare @from and to date range variables
DECLARE @from DATETIME
DECLARE @to DATETIME

-- This code line is based on Andomar's answer
SET @from = DATEADD(month,DATEDIFF(month, 0, @date),0) 

-- Just simply to variable @from adds 1 month, minus 1 second
SET @to = DATEADD(second, -1, DATEADD(month, 1, @date))

-- Result
SELECT @from, @to

You will get result like 2012.01.01 00:00:00 - 2012.01.31 23:59:59.