Find the first and last date of any month given the Month in Number and Year

Niladri.Biswas
Posted by Niladri.Biswas under Sql Server category on | Points: 40 | Views : 1537
Recently, I had a requirement where I need to get the first and last date of any month provided that the Month number and Year is given. Here are some of the approaches

-- Input
DECLARE @Month int = 12, @Year int = 2012


--Approach 1:
SELECT  
FirstDateOfMonth = CONVERT(VARCHAR(10), DATEADD(M,@Month-1,DATEADD(YY,@Year-1900,0)) ,110)
,LastDateOfMonth = CONVERT(VARCHAR(10), DATEADD(DD,-1,DATEADD(M,@Month,DATEADD(YY,@Year-1900,0))),110)


-- Approach 2:
DECLARE @StartDate DATETIME = CAST(@Month AS VARCHAR(2)) + '/' + '01/' + CAST(@Year AS VARCHAR(4)) -- Must be in MM/DD/YYYY format
SELECT
FirstDateOfMonth = CONVERT(VARCHAR(10),@StartDate,110)
,LastDateOfMonth = CONVERT(VARCHAR(10),DATEADD(DD,-DAY(@StartDate),DATEADD(M,1,@StartDate)),110)


-- Approach 3:
SELECT 
FirstDateOfMonth = CONVERT(VARCHAR(10),@StartDate,110)
,LastDateOfMonth = CONVERT(VARCHAR(10),DATEADD(M, 1, CONVERT(DATETIME, CAST(@Month AS VARCHAR)+ '/01/' + CAST(@Year AS VARCHAR))) - 1,110)


-- Approach 4:
SELECT	FirstDateOfMonth = CONVERT(VARCHAR(10),@StartDate,110)
,LastDateOfMonth = CONVERT(VARCHAR(10),DATEADD(S,-1,DATEADD(MM, DATEDIFF(M,0,@StartDate)+1,0)),110)



-- Approach 5:
SELECT	FirstDateOfMonth = CONVERT(VARCHAR(10),@StartDate,110)
,LastDateOfMonth = CONVERT(VARCHAR(10),DATEADD(MS, -3, DATEADD(MM, DATEDIFF(M, 0, @StartDate) + 1, 0)),110)



-- Approach 6:
SELECT	FirstDateOfMonth = CONVERT(VARCHAR(10),@StartDate,110)
,LastDateOfMonth = CONVERT(VARCHAR(10),DATEADD(DD, -1, DATEADD(MM, 1, @StartDate)),110)


--Approach 7: (Denali's EOMonth)
SELECT	FirstDateOfMonth = CONVERT(VARCHAR(10),@StartDate,110)
,LastDateOfMonth = CONVERT(VARCHAR(10),EOMonth(@StartDate),110)



N.B.~ Suppose the Month Name is given instead of Month number e.g. JAN or FEB say. In that case we can convert to the month name to month number and proceed as shown under

SELECT MonthName = 'JAN', MonthNumber = DATEPART(MM,CAST('JAN'+ ' 1900' AS DATETIME))


OR form a kind of lookup table

;WITH MonthLookup AS
(
SELECT MonthName = 'JAN', MonthNumber = 1 UNION ALL
SELECT MonthName = 'FEB', MonthNumber = 2 UNION ALL
SELECT MonthName = 'MAR', MonthNumber = 3 UNION ALL
SELECT MonthName = 'APR', MonthNumber = 4
)
SELECT MonthName,MonthNumber
FROM MonthLookup
WHERE MonthName = 'JAN'

Hope this will be helpful

Comments or Responses

Login to post response