query for first and last day of month

sql, sql-server

Solution

Try this :-

DECLARE @StartDate  DATETIME,
        @EndDate    DATETIME;

SELECT   @StartDate = '20110501'        
        ,@EndDate   = '20110801';


SELECT  DATENAME(MONTH, DATEADD(MONTH, x.number, @StartDate)) AS MonthName,
CONVERT(VARCHAR(25),
DATEADD(dd,-(DAY(DATEADD(MONTH, x.number, @StartDate))-1),DATEADD(MONTH, x.number, @StartDate)),101) as FirstDay,
CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,DATEADD(MONTH, x.number, @StartDate)))),DATEADD(mm,1,DATEADD(MONTH, x.number, @StartDate))),101) as LastDay
FROM    master..spt_values x
WHERE   x.type = 'P'        
AND     x.number <= DATEDIFF(MONTH, @StartDate, @EndDate)

Result :-

 MonthName       FirstDay         LastDay   
  May           05/01/2011        05/31/2011    
 June           06/01/2011        06/30/2011    
 July           07/01/2011        07/31/2011    
 August         08/01/2011        08/31/2011

Result obtained taking the help from this query

Problem

If I have the name of the month, how can I have the first and last day of that month in SQL? I have this query to returns the month names: ``` DECLARE @StartDate DATETIME, @EndDate DATETIME; SELECT @StartDate = '20110501' ,@EndDate = '20110801'; SELECT DATENAME(MONTH, DATEADD(MONTH, x.number, @StartDate)) AS MonthName FROM master.dbo.spt_values x WHERE x.type = 'P' AND x.number <= DATEDIFF(MONTH, @StartDate, @EndDate) ``` Result: Now, how can i get the first and last day of that months? changing the query.

Original source