EOMONTH function in SQL Server 2012 (End of Month in SQL Server 2012)

The syntax is : EOMONTH ( start_date [, month_to_add ] ).

You can use this function along with dateadd function to get the beginning of the day, yesterday, and tomorrow. You can also use the same way to get the beginning of the month, last month or next month.

DECLARE @myDateTime Datetime = getdate()

SELECT dateadd(day,day(@myDateTime),EOMONTH (@myDateTime,-1)) as BeginningOfToday

,dateadd(day,day(@myDateTime)-1,EOMONTH (@myDateTime,-1)) as BeginningOfYesterday

,dateadd(day,day(@myDateTime)+1,EOMONTH (@myDateTime,-1)) as BeginningOfTomorrow

,dateadd(day,1,EOMONTH (@myDateTime,-1)) as BeginningOfThisMonth

,dateadd(day,1,EOMONTH (@myDateTime)) as BeginningOfNexMonth

,dateadd(day,1,EOMONTH (@myDateTime,-2)) as BeginningOfLastMonth

If you don’t use this new function you can use the following function to get the begining of today or yesterday:

SELECT DATEADD(day, DATEDIFF(day,0,GETDATE())-1,0) as BeginningOfYesterday

, DATEADD(day, DATEDIFF(day,0,GETDATE()),0) BeginningOfToday


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s