Finding END of Month in SQL Server Denali Using EOMONTH function

Today we are going to see a new datetime function that’s introduced with SQL Server Denali. This function is expected by many of the developers, this function will let you know the last date of a month. It’s EOMONTH(), you can pass the date as input , it will give you the end date of that month.

Syntax is below

EOMONTH ( start_date [, month_to_add ] )

Let’s try this function

DECLARE @todaydate datetime=GETDATE()
--End of Month
SELECT EOMONTH(@todaydate)
--End of Month, adding 6 months to the date
SELECT EOMONTH(@todaydate,6)
--End of Month, going back by 6 months to the date
SELECT EOMONTH(@todaydate,-6)
--You can also give date time input directly to get
SELECT EOMONTH('10-03-1965')

Cool right. Now you wonder is there any new function for beginning of the month? Ah! that’s not added to Denali because if you think logically all the month begin with 01 date only so there is no need for this function however you can still utilize EOMONTH function to retrieve the beginning date of the month, sample query is below

--Script to get beginning of the month
SELECT DATEADD(D,1,EOMONTH(GETDATE(),-1))