11

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 this info?

Ristogod
  • 905
  • 4
  • 14
  • 29

10 Answers10

21

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".

DOK
  • 32,337
  • 7
  • 60
  • 92
  • This worked. Although I chose a different answer as I preferred its syntax. The page you linked is a great source of information. Thanks. – Ristogod Oct 12 '10 at 15:57
17
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.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
2

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())
jam
  • 3,640
  • 5
  • 34
  • 50
patrudu
  • 21
  • 1
2
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.

techraf
  • 64,883
  • 27
  • 193
  • 198
Lalesh Mishra
  • 21
  • 1
2
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;
John Hartsock
  • 85,422
  • 23
  • 131
  • 146
  • This worked. Although I chose a different answer as I preferred its syntax and non-use of converting. Thanks. – Ristogod Oct 12 '10 at 15:56
2
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
AdaTheDev
  • 142,592
  • 28
  • 206
  • 200
  • This worked. Although I chose a different answer as I preferred its syntax and non-use of Right. Thanks. – Ristogod Oct 12 '10 at 15:55
2

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;
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • This worked. Although I chose a different answer as I preferred its syntax and non-use of casting. Thanks. – Ristogod Oct 12 '10 at 15:55
2
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)
Manish
  • 517
  • 1
  • 3
  • 19
Crash
  • 21
  • 1
0

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
Mohamed Thaufeeq
  • 1,667
  • 1
  • 13
  • 34
0

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)

Hardik Solgama
  • 13
  • 3