Get the date range for the current month?

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

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

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.

If you need the month range just for checking if "check_date" belongs to a specific month, you maybe can use a condition like

month(Check_date) = @Month and year(Check_date) = @Year

You could create a function that returns the end date for the given date.

So the function you can pass the start date and it will be like this

WHEN(MONTH(@date) IN (1, 3, 5, 7, 8, 10, 12)) THEN 31
WHEN(MONTH(@date) IN (4, 6, 9, 11)) THEN 30
ELSE 
    CASE 
       WHEN (YEAR(@date) % 4 = 0
       AND YEAR(@date) % 100 != 0)
       OR (YEAR(@date) % 400  = 0)
       THEN 29
        ELSE 28
END