How to get last day of the month in T-SQL

If you need to get last day of the month (current month or any other) on SQL Server, use in-built function EOMONTH.

SELECT EOMONTH(GETDATE())

SELECT EOMONTH('2011-06-11')

In examples above, first select statement will give you current month’s last day, and second statement will give you last day of July in 2011.

You can then combine this with DATENAME function to get day of the week name.

SELECT DATENAME(dw, EOMONTH(GETDATE()))

SELECT DATENAME(dw, EOMONTH('2011-06-11'))

First select statement returns Saturday as last day of the current month, and second statement returns Thursday as last day of the July 2011.

Further reading:

EOMONTH on MSDN

DATENAME on MSDN

Advertisements

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