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.

This was last published in September 2002

## Content

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

#### Have a question for an expert?

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.

## SearchDataManagement

To date, the Hadoop data scientist has had to be a superhero. Self-service tools will change that, but not overnight.

• ### IBM bets on Hortonworks Hadoop platform as its big data engine

IBM pulled the plug on its distribution of Hadoop in favor of reselling Hortonworks' bundle of big data technologies, a decision ...

• ### High-level tools help Hadoop data lake go to school

Moving relational data into Hadoop is not a slam dunk. To avert programming complexities, a major Texas school turned to ...

• ### Exploring Oracle Advanced Analytics

Oracle Advanced Analytics and other data analytics tools, including Oracle R Enterprise and Oracle Data Miner, enable business ...

• ### Cloud-based BI and analytics systems start taking a firmer shape

As cloud computing becomes more common, there are signs that companies increasingly are looking at whether cloud BI and analytics...

• ### Despite functionality gains, use of cloud BI tools elevates slowly

Running business intelligence applications in the cloud has yet to take the BI world by storm. Consultant Howard Dresner offers ...

## SearchSAP

• ### Next-generation technologies catching on despite SAP Leonardo rebrand

Call them what you will, next-generation technologies, like blockchain and machine learning, had their own growth paths before ...

• ### SAP S/4HANA ERP platform options expand but raise deployment questions

Most SAP users haven't started to implement S/4HANA, but this is changing as they better understand the S/4HANA roadmap, ...

• ### SAP makes choosing S/4HANA modules easier, but path unclear for some

Despite SAP's insistence that code base is the same, analysts and users say different S/4HANA on-premises and cloud versions can ...

## SearchSQLServer

• ### A closer look at Python-SQL Server 2017 integration

Do you know everything you need to take advantage of SQL Server 2017's support of Python?

• ### How much do you really know about SQL Server 2016 licensing?

Should you use Server + CAL licensing for SQL Server 2016 or does Per Core licensing offer more bang for your buck? Read our ...

## TheServerSide

• ### How microservices patterns made Uber's architecture perform better

How did Uber manage to deal with the inevitable Halloween rush? Applying microservices patterns to their architecture played a ...

• ### Effective DevOps hinges on automating a continuous delivery pipeline

Many organizations struggle to bring development and operations together, only to realize that effective DevOps means ...

• ### How cheap eye tracking software changes the app development game

Find out what the advent of low-cost eye tracking software means for the future of both marketing and application development.

## SearchDataCenter

• ### Server firmware security threat, real or not, gets renewed attention

The latest threat to server security could be the firmware. That's the emphasis of at least one server-maker, hoping to help ...

• ### Assess and compare DCIM software options in the market

DCIM software can improve the management and operation of your data center. Choose the right vendor for your company to control ...

• ### Evaluate DCIM software and its impact on the data center

Buyers must know how to evaluate DCIM software to ensure it meets their requirements for asset tracking and management, power and...

## SearchContentManagement

• ### Three BI dashboard best practices you need to know

As companies struggle with BI integration, Microsoft has figured out the magic formula with its Power BI dashboard. Here are the ...

Adobe rounds out its cloud suite with Adobe Scan, which allows mobile employees to capture documents and images and turn them ...

• ### Five SQL 2017 improvements you need to know about

SQL Server 2017 has made 5 key improvements that can help integrate AI capabilities and analytics into the enterprise more ...

## SearchFinancialApplications

• ### Ceridian uses employee engagement tool from its own joint venture

After forming a new joint venture, LifeWorks, HCM vendor Ceridian uses LifeWorks' employee recognition, engagement and perks ...

• ### Evaluate HR technology to fit your talent management processes

Choose your talent management system wisely or risk irking your employees. Learn the criteria for evaluating software that ...

• ### SaaS HR software dominates as cloud HCM systems proliferate

HCM systems for big employers are increasingly in SaaS form and incorporating a wide range of HR tech beyond core functions, such...

Close