SQL EOMONTH 3

SQL EOMONTH

by

The SQL EOMONTH is one of the Date Function is used to display the last month of a given date. This EOMONTH function allows you to add a second argument (optional) to move forward and backward.

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), the SQL Server EOMONTH function will move forward to a specified number of months. And display the last date of that month.
  • If you specify the Negative integer as the second argument (Number), the EOMONTH function will move backward to a specified number of months and display the last date of that month.

SQL EOMONTH Example 1

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

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

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 the 8th month (or August)

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

SQL EOMONTH with Positive and Negative integer

In this Sql Server EOMONTH 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

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]
SQL EOMONTH 2

We used two arguments in EOMONTH function, and the second argument is a positive integer. So, the following statement will move forward to 2 months. It means (8 + 2 = 10) SQL Server EOMONTH will find the last day of the 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, the following statement will move backward to 1 month. It means (8 – 1 = 7) SQL Server EOMONTH will find the last day of the 7th month (or July).

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

SQL EOMONTH Example 2

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

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]
SQL EOMONTH 3

Comments are closed.