Latest Post

Friday, 7 October 2016

Sql Server - Date and Time function - EOMONTH()

EOMONTH ():- This is new date time function that is introduced in SQL server 2012. This function can be used to get the last day of the month. We can Use of EOMONTH() function to find last day Previous and Next Month. 

Syntax:-
EOMONTH (start_date)

Examples:- follwong are the diffrent type of exaples

Example 1: Find last day of the month

SELECT EOMONTH(GETDATE()) LastDayofMonth


Result:-

LastDayofMonth
---------------
2016-10-31

Example 2: Find last day of the month during Leap year

SELECT EOMONTH('20070201') LastDayofNonLeapYearFeb,
          EOMONTH('20080201') LastDayofLeapYearFeb,
          EOMONTH('20090201') LastDayofNonLeapYearFebLastDay;



Result:-

LastDayofNonLeapYearFeb LastDayofLeapYearFeb    LastDayofNonLeapYearFebLastDay
==============================================================================
2007-02-28               2008-02-29               2009-02-28



Example 3: Find last day Previous and Next Month

SELECT EOMONTH(GETDATE(),-1) LastDayOfPreviousMonth,
          EOMONTH(GETDATE(),1) LastDayOfNextMonth;

Result:-

LastDayOfPreviousMonth  LastDayOfNextMonth
==========================================
2016-09-30               2016-11-30



Example 4: Find last day Previous and Next Month

SELECT DATENAME(dw,EOMONTH(GETDATE())) LastDayofMonthDay;


Result:-

LastDayofMonthDay
=================
Monday



Get First Date of Next Week
SELECT DATEADD(d,1,EOMONTH(GETDATE())) FirstDayofNextMonth;
Result:-

FirstDayofNextMonth
==================
2016-11-01