Q
Problem solve Get help with specific problems with your technologies, process and projects.

Last day of the month

Sir, I want the last day of the month given the name or number of the month. How can I go about it?

There's no special function for last day of the month, but you can calculate it. The strategy is to get the next month, get the first day of that month, then subtract one day.

As you may know, standard SQL provides a number of date functions, but, sadly, every database has its own functions. Therefore I will explain the "last day of the month" algorithm using Microsoft SQL/Server date functions.

For demonstration purposes, let's use the current date, which is provided by the GETDATE() function. The first step is to add 1 month --

dateadd(mm,1,getdate())

We use the DATEADD() function, rather than trying to extract the month number from the current date, simply to avoid having to deal with the case of the next month being January. Now we need to get the first day of this next month. Convert the date to a yyyy-mm string using the CONVERT() function with style 120 and truncating the result to 7 characters --

convert(char(7),dateadd(mm,1,getdate()),120)

Now concatenate '-01' onto the end of this yyyy-mm string, and convert it back to datetime format using CAST() --

cast(convert(char(7),dateadd(mm,1,getdate()),120)+'-01' as datetime)

Now that we have the first day of the next month as a datetime value, subtract 1 day and the result is a datetime value for the last day of this month --

dateadd(dd,-1,cast(convert(char(7),dateadd(mm,1,getdate()),120)+'-01' as datetime))

If you want just the last day, use the DAY() function on this date --

day(dateadd(dd,-1,cast(convert(char(7),dateadd(mm,1,getdate()),120)+'-01' as datetime)))

Note that you do not have to do all those steps -- I only showed you the process in steps so you could see how the functions nest. Just use the last or second last expression in your query.

Your original question was "given the name or number of the month" and of course this requires a slight variation on the above, to construct a date rather than using GETDATE(). Notice, though, that you would also need to know the year, if the month is February.

For More Information


This was last published in September 2002

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close