query for first and last day of month

Question

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.

Answer 1

If you want a more general and simple solution:

DECLARE @startdate AS DATETIME = GETDATE()
SELECT      
DATEADD(MONTH, DATEDIFF(MONTH, 0, @startdate) , 0)   as startMonth,
DATEADD(SECOND, -1, DATEADD(MONTH, 1,  DATEADD(MONTH, DATEDIFF(MONTH, 0, @startdate) , 0) ) ) as endMonthExactly,
EOMONTH(@startdate) as endMonthDay

Gives the result:

startMonth       2023-12-01 00:00:00
endMonthExactly  2023-12-31 23:59:59
endMonthDay      2023-12-31