Home » SQL EOMONTH()

SQL EOMONTH()

SQL EOMONTH() function is a date function, it returns the last day of the month of a specified date, with an optional offset.





SYNTAX

EOMONTH (Date, [,offset])

Date is a date that is used to get a last day of month.

Second argument offset is an integer that specifies the number of months to add to the date .

Positive offset value returns next month’s last day and Negative offset value returns previous month’s last day.

Lets look at an example of function EOMONTH() in SQL Server.

Using EOMONTH Function to get last day of the month of specific date

Following statement, uses EOMONTH function and returns the last day of the month for given date.

DECLARE @orderdate AS DATETIME
SET @orderdate ='1996-07-15 00:00:00.000' 
SELECT EOMONTH(@orderdate) AS [Last Day Of Month]

As you can see, It returns a last day of the month for date =‘1996-07-15 00:00:00.000’. Here we do not provide an offset as we want the last day of given date only.

Using EOMONTH function to get last day of next month from given date

Following statement uses EOMONTH function, and returns the last day of next month from given date.

Here we have provided an offset value 1, that means EOMONTH month adds 1 month to the input date ‘1996-07-15 00:00:00.000’ and returns next month last day.

DECLARE @orderdate AS DATETIME
SET @orderdate ='1996-07-15 00:00:00.000' 
SELECT EOMONTH(@orderdate, 1) AS [Last Day Of Next Month]

As you can see, the output of first statement is the last day of previous month that is JUNE as month of current given date is JULY so the output is ‘1996-06-30’.
In similar way the output of second statement is the last day of third previous month that is APRIL as month of current given date is JULY so the third previous month would by APRIL and the last day of April would be  ‘1996-04-30’.



If you only want a day part from date then you can use DAY() function to get the last day only from date.
DECLARE @orderdate AS DATETIME
SET @orderdate ='1996-07-15 00:00:00.000' 
SELECT DAY(EOMONTH(@orderdate,-1)) AS [Last Day Of Previous Month 1] 
select DAY(EOMONTH(@orderdate,-3)) as [Last Day Of Previous Month 2]
Also Read..
SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 2,463 total views,  12 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.