SQL SERVER – Script/Function to Find Last Day of Month

Following query will find the last day of the month. Query also take care of Leap Year.

Script:

DECLARE @date DATETIME
SET @date='2008-02-03'
SELECT DATEADD(dd, -DAY(DATEADD(m,1,@date)), DATEADD(m,1,@date))
            AS LastDayOfMonth
GO
DECLARE @date DATETIME
    SET @date='2007-02-03'
SELECT DATEADD(dd, -DAY(DATEADD(m,1,@date)), DATEADD(m,1,@date))
            AS LastDayOfMonth
GO
ResultSet:
LastDayOfMonth
-----------------------
2008-02-29 00:00:00.000

(1 row(s) affected)

LastDayOfMonth
-----------------------
2007-02-28 00:00:00.000

(1 row(s) affected)

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

11 thoughts on “SQL SERVER – Script/Function to Find Last Day of Month

  1. Pingback: SQL SERVER - Find Last Day of Any Month - Current Previous Next Journey to SQL Authority with Pinal Dave

  2. Pingback: SQL SERVER – Function: Is Function – SQL in Sixty Seconds #004 – Video « SQL Server Journey with SQL Authority

  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #030 | SQL Server Journey with SQL Authority

  4. also the EOMONTH function from the commericial package XLeratorDB… http://tinyurl.com/eomonth

    Use EOMONTH to calculate the date for the last day of the month that is the indicated number of months before or after the start date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.

    Like

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