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

Question

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?

Eugeniaeugenics

Answer 1

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.

Barbusse

Thank you. This worked. I preferred this answer as it appears to be the cleanest. – Eugeniaeugenics

Answer 2

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

Ingalls

This worked. Although I chose a different answer as I preferred its syntax. The page you linked is a great source of information. Thanks. – Eugeniaeugenics

Answer 3

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;

Stationmaster

This worked. Although I chose a different answer as I preferred its syntax and non-use of converting. Thanks. – Eugeniaeugenics

Answer 4

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

Glairy

This worked. Although I chose a different answer as I preferred its syntax and non-use of Right. Thanks. – Eugeniaeugenics

Answer 5

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;

Lipid

This worked. Although I chose a different answer as I preferred its syntax and non-use of casting. Thanks. – Eugeniaeugenics

Answer 6

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)

Foreplay

Answer 7

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())

Confine

Answer 8

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.

Firman

Answer 9

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)

Headlong

Answer 10

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

Whisk

Answer 11

How to fetch First day and Last day of the month:

SELECT cast(DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) as date) as [First Day Of Month],cast(DATEADD(ms, -3, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0)) as date) as [Last Day Of Month]

These methods should help you fetch the first and last days of the month in your preferred environment.

Diatribe