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 --
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 --
Now concatenate '-01' onto the end of this
yyyy-mm string, and convert it back to datetime format using
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
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.