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