CODEWITHSUNDEEP
sqlsql-serverdelphidelphi-7
IElite
IElite

Reputation: 1848

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

Upvotes: 3

Views: 17338

Answers (4)

user1551853
user1551853

Reputation:

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.

Upvotes: 1

Vitor Furlanetti
Vitor Furlanetti

Reputation: 451

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

Upvotes: 0

mslliviu
mslliviu

Reputation: 1138

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

Upvotes: 0

Andomar
Andomar

Reputation: 238116

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

Upvotes: 12

Related Questions