Tutorial Gateway

SQL EOMONTH

by

The SQL EOMONTH is one of the Date Function is used to display the last month of a given date. This function allows you to add second argument (which is optional) to move forward and backward. In this article we will show you, How to use SQL Server EOMONTH function with live example.

SQL EOMONTH Syntax

The basic syntax of SQL Server EOMONTH function is as shown below:

EOMONTH (Date, Months to add)

Date: Please specify the valid date. It can be column data, expression or any variable.

Months to add: This is an optional argument. Please specify the number of months you want to move:

  • If you specify the positive integer as the second argument (Number), EOMONTH function will move forward to specified number of months and display the last date of that month.
  • If you specify the Negative integer as the second argument (Number), EOMONTH function will move backwards to specified number of months and display the last date of that month.

SQL EOMONTH Example 1

In this example we are going to declare a variable of datetime2 data type and we are going to find the last day (in a month) of that variable date

SQL CODE

DECLARE @Date datetime2 = '2015-08-25 14:24:04.1234567'
     
SELECT @Date AS Today, 
       EOMONTH(@Date) AS [Last Day of this Month]

OUTPUT

SQL EOMONTH 1

ANALYSIS

We declared a variable called @Date and assigned the date and time to that variable

DECLARE @Date datetime2 = '2015-08-25 14:24:04.1234567'

EOMONTH function present in the following statement will find the last day of 8th month (or August)

EOMONTH(@Date) AS [Last Day of this Month]

SQL EOMONTH with Positive and Negative integer

In this example we are going to use the positive and negative integers as a second argument to show you the last days of a parallel period

SQL CODE

DECLARE @Date datetime2 = '2015-08-25 14:24:04.1234567'
     
SELECT @Date AS Today
     , EOMONTH (@Date, 2) AS [Last Day] 
     , EOMONTH (@Date, -1) AS [Previous Month]

OUTPUT

SQL EOMONTH 2

ANALYSIS

We declared a variable called @Date and assigned the date and time to that variable

DECLARE @Date datetime2 = '2015-08-25 14:24:04.1234567'

We used two arguments in EOMONTH function and the second argument is positive integer So, following statement will move forward to 2 months. It means (8 + 2 = 10) SQL Server EOMONTH will find the last day of 10th month (or October)

EOMONTH(@Date, 2) AS [Last Day]

We used two arguments in EOMONTH function and this time we used Negative integer as a second argument So, following statement will move backward to 1 month. It means (8 – 1 = 7) SQL Server EOMONTH will find the last day of 7th month (or July)

EOMONTH(@Date, -1) AS [Previous Month]

SQL EOMONTH Example 2

In this example we are going to use the Custom SQL query and find out the Last day of current Month, Previous period and Next period using EOMONTH function.

SQL CODE

SELECT [FirstName] + ' '+ [LastName] AS [Full Name]
      ,[Occupation]
      ,[YearlyIncome]
      ,[HireDate]
      ,EOMONTH([HireDate]) AS [This Month]
      ,EOMONTH([HireDate], 2) AS [Next Period]
      ,EOMONTH([HireDate], -2) AS [Previous Period]
 FROM [SQL Tutorial].[dbo].[Employee]

OUTPUT

SQL EOMONTH 3

Thank You for Visiting Our Blog

Placed Under: SQL