This is a difficult question to answer, and it depends on what counts as "used in battle". I am going to assume you do not mean as supply animals, as they were often used as beasts of burden, even into the twentieth century. Now, the why is actually easier to answer than the when, so I will start there.

The first thing I want to point out is that elephants were probably not nearly as effective as that video claims. Elephants may have been great physiological weapons, towering beasts with giant tusks many soldiers may have thought to be monsters from their mythologies, but the elephants were very much afraid of people. Despite being large, they have very sensitive skin and would try to flee when attacked by the hail of arrows and javelins such a large target would attract. Mahouts had to used a hook and spears to force an elephant to move towards the enemies, and would sometimes have to kill their mounts if it went berserk and began to trample their own men. As armies began to adapt to fighting elephants, even more weaknesses were discovered. Pliny the Elder wrote that elephants were scared by the sound of pigs, and claimed that pigs were covered in oil and lit on fire before being hurled at elephants, scaring them off. Additionally, only mature male elephants could be used in battles against other elephants, as females and young males will flee from bulls.

Despite these disadvantages, elephants were still used for a long time, however, so they clearly had some application. As I mentioned above, the mere sight of these beasts was terrifying, especially to soldiers from regions that didn't have them, and there was a great deal of prestige attached to them in South Asian cultures, with kings often using the number of elephants they owned as sign of their wealth and power. Despite their instincts, they could be trained to charge into formations, and unlike horses the sheer size of elephants meant that no matter how well trained, no matter how disciplined, now matter how well equipped, any formation of spears or pikes would quite literally collapse due to the force of an elephant smashing into them. Elephants also proved quite useful as mobile archery platforms, and many generals liked to sit atop elephants to get a better view of the battlefield.

The downfall of war elephants was gunpowder. The loud sounds of muskets scared elephants, and once cannons became common on the battlefield, armies finally had a reliable way to kill opposing elephants safely without having any of their own men trampled in the process. What probably made it even less common was the increasing rarity of the animals themselves. Elephants had always been difficult animals to obtain, as they can only be caught wild due to a 22 month gestation and an aging process that takes decades to reach full maturity preventing domestication. Elephants have to be raised with their mahouts from a young age, and they were incredibly hard to maintain outside of the regions where they came from. Further complicating things, bull elephants experience musth, periods of extreme aggression when they are all but impossible to control. All of this was worsened by the falling numbers of elephants as deforestation, hunting and the demand for captive elephants itself caused the Asian elephant population to fall to the thousands by the beginning of the twentieth century. While African elephants (at the time) did not experience such a dramatic reduction in numbers, they are much more difficult to train, and the northern forest subspecies which was the source of Carthage's famed beasts did suffer a dramatic decline.

So to answer your question, 1800 is actually a good rough date for when war elephants began to disappear, but it is important to note they were not as common as some people think. They were more of a novelty, something to display a leader's power, but they were just too rare to be a part of every battle, and were more commonly used as beasts of burden, both for armies and for peaceful uses, such as logging.

What would happen if 2 elephants of opposing sides encountered each other in battle? Not what the people on top of the elephant would do, of course (that's pretty obvious), but what did the animals do?