SQL Server 2005 Get First and Last date for any Month in any Year

I have a stored procedure that has to accept a month as int (1-12) and a year as int. Given those two values, I have to determine the date range of that month. So I need a datetime variable to represent the first day of that month, and another datetime variable to represent the last day of that month. Is there a fairly easy way to get th开发者_JAVA百科is info?

First day of the month: SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)

Last day of the month: SELECT DATEADD(ms, -3, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0))

Substitute a DateTime variable value for GETDATE().

I got that long ago from this very handy page which has a whole bunch of other date calculations, such as "Monday of the current week" and "first Monday of the month".

DECLARE @Month int
DECLARE @Year int

set @Month = 2
set @Year = 2004

select DATEADD(month,@Month-1,DATEADD(year,@Year-1900,0)) /*First*/

select DATEADD(day,-1,DATEADD(month,@Month,DATEADD(year,@Year-1900,0))) /*Last*/

But what do you need as time component for last day of the month? If your datetimes have time components other than midnight you may well be better off just doing something like

WHERE COL >= DATEADD(month,@Month-1,DATEADD(year,@Year-1900,0)) 
     AND COL < DATEADD(month,@Month,DATEADD(year,@Year-1900,0)) 

In this way your code will continue to work if you eventually migrate to SQL Server 2008 and the greater precision datetime datatypes.

DECLARE @Month INTEGER
DECLARE @Year INTEGER
SET @Month = 10
SET @Year = 2010

DECLARE @FirstDayOfMonth DATETIME
DECLARE @LastDayOfMonth DATETIME

SET @FirstDayOfMonth = Str(@Year) + RIGHT('0' + Str(@Month), 2) + '01'
SET @LastDayOfMonth = DATEADD(dd, -1, DATEADD(mm, 1, @FirstDayOfMOnth))
SELECT @FirstDayOfMonth, @LastDayOfMonth

Try this:

Declare @month int, @year int;
Declare @first DateTime, @last DateTime;
Set @month=10;
Set @year=2010;
Set @first=CAST(CAST(@year AS varchar) + '-' + CAST(@month AS varchar) + '-' + '1' AS DATETIME);
Set @last=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@first)+1,0));

SELECT @first,@last;

DECLARE @Month int;
DECLARE @Year int;
DECLARE @FirstDayOfMonth DateTime;
DECLARE @LastDayOfMonth DateTime;

SET @Month = 3
SET @Year = 2010

SET @FirstDayOfMonth = CONVERT(datetime, CAST(@Month as varchar) + '/01/' + CAST(@Year as varchar));
SET @LastDayOfMonth = DATEADD(month, 1, CONVERT(datetime, CAST(@Month as varchar)+ '/01/' + CAST(@Year as varchar))) - 1;

select [FirstDay Of The Month] as Text ,convert(varchar,dateadd(d,-(day(getdate()-1)),getdate()),106) 'Date'
union all
select [LastDay Of The Month], convert(varchar,dateadd(d,-day(getdate()),dateadd(m,1,getdate())),106)

Current month last Date:

select dateadd(dd,-day(dateadd(mm,1,getdate())),dateadd(mm,1,getdate()))

Current month 1st Date:

select dateadd(dd,-day(getdate())+1,getdate())

DECLARE @Month  int = 3, @Year  int = 2016
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

-- First date of month
SET @StartDate = DATEADD(MONTH, @Month-1, DATEADD(YEAR, @Year-1900, 0));
-- Last date of month
SET @EndDate = DATEADD(SS, -1, DATEADD(MONTH, @Month, DATEADD(YEAR, @Year-1900, 0)))

This will return first date and last date with time component.

You can use DATEFROMPARTS to declare the first day of the month and EOMONTH to derive the last date of the month from the first date.

DECLARE @Month INT = 1
DECLARE @Year INT = 2020

DECLARE @FromDate DATE = DATEFROMPARTS(@Year, @Month, 1)
DECLARE @ToDate DATE = EOMONTH(@FromDate)

SELECT @FromDate MonthFirstDate, @ToDate MonthLastDate

you can use this format for the current month's start date to end date, so I wrote for the fetch date only.

Start date of Month is : SELECT CAST(DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) AS Date)

End Date is : SELECT CAST(DATEADD(ms, -3, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0)) AS Date)