Find out Last day of a month

In SQL Server 2012, there is a function called EOMONTH which returns the last day of a month

Let us consider the following example

DECLARE @DATE DATETIME
SET @DATE='20040211'
SELECT EOMONTH(@DATE)

The result is

last_day
----------
2004-02-29

In earlier versions you can use the following methods to find out it

Method 1 : Find first day of next month and subtract 1 day

DECLARE @DATE DATETIME
SET @DATE='20040211'
SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,@DATE)+1,-1) AS LAST_DAY

Method 2 : Subtract day part from date and add 1 month

DECLARE @DATE DATETIME
SET @DATE='20040211'
SELECT DATEADD(MONTH,1,DATEADD(DAY,-DAY(@DATE),@DATE)) AS LAST_DAY

Advertisements

2 thoughts on “Find out Last day of a month

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