I don't get it quite yet, since it seems to me like you can play most everything in standard tuning with a bit of practice. Or can you? What are some parameters to look for in a specific guitar tuning to decide whether it fits your playing or a composition?
The guitar and similar fretted stringed instruments can be extremely versatile - but have inherent limitations to what chords or series of notes can be efficiently played on them using any given tuning. The accepted "standard tuning" in use today accommodates a very broad spectrum of possibilities, but some composers might wish to deviate from what is possible or practical using standard tuning. You could call it "thinking out side the box".
Also, some tunings make certain phrases on guitar easier to play by changing the location of where certain notes can be found on the fretboard and lining them up strategically.
Since most composers stick to the standard formula for tuning, most songs written on or for guitar can be easily played using standard tuning. But if the composer is looking for something different, he/she may choose to use an alternate tuning to achieve whatever particular effect or musical nuance they envision. Or he/she might just want to write music that sounds radically different from the mainstream.
So for playing most covers of most songs, standard tuning is your best option. However, although some songs written in an alternative tuning can be played using standard tuning, it is usually easier to play them authentically note for note - using the tuning the song was composed in. And some alternative tunings allow for musical phrases that simply cannot be played with standard tuning without superhuman powers.
There are many very popular standard tunings that are commonly used such as Drop D, Open G, Open E, DADGAD and others. One form of alternative tuning - open tunings - allow you to play a chord by playing all strings open. They also allow for a single finger barre to form other chords in the key of the open tuning and therefore work well for slide guitar.
Many open tunings are limited in versatility in that they work great for the key they are optimized for, but make playing outside that key much more difficult (unless you use a capo).
If you want to play a certain song on guitar that was written in a particular alternate tuning, you can either attempt to derive an acceptable (if less than authentic) substitute way to play a passable version of the song using standard tuning - or you can learn to play the song using the tuning that it was written in.
Here is an on line guide to some common alternate tunings for guitar Alternate Tunings For Guitar. There are many others.
The best way to learn an alternative tuning is to discover a song you love and want to learn to play, that uses an alternative tuning, and then learn to play that song in the tuning it was created in. Then learn more songs in that same tuning. Later you can add another alternative tuning to your arsenal by repeating the process for another song that utilizes a different alternative tuning.
Alternative tunings are a great way to keep the guitar interesting and if you are a composer, they are a great way to discover new and unique musical ideas.
One thing not mentioned by the existing answers is tone. Changing tuning changes the tonal response of your guitar.
If you lower or raise a particular string, the tension of that string is changed, and this can dramatically change the overtones that ring out.
Using open strings that are unconventionally tuned also allows for a wide range of tones. Even unplayed strings will resonate, so deciding what notes they resonate with changes the overall timbre.
Many alternate tunings are about making use of open strings or lining up certain notes (ex. chord tones) to the same fret so that they can be more easily barred. For example if you are playing with a slide it's much easier if the notes you want to play from different strings fall on the same fret.
Another benefit I've found is that it breaks pattern-based ruts that you've developed. The nature of the fretboard is very movable and pattern-based. You can play a chord shape or single note pattern in one place and just move to a different fret to play it in a different key. This is helpful but it leads guitarists to use these patterns as a bit of a crutch that can cause creative ruts. When you play in a different tuning suddenly all those patterns are invalidated and you are forced to listen and think again rather than relying on muscle memory and recycled riffs (or you can play the same patterns and suddenly they'll sound completely different for better or worse).
That said I usually stick to standard tuning these days.
Sometimes normal tuning requires you to press alot more strings than you would have to if you would just tune it differently to match your tune.
So yes, you can play "anything" but it'll be harder to form the chords/harmony. This is especially true in fingerstyle guitar where one does both melody/harmony at the same time. Open tuning and/or other alternate tuning allow you to drone on strings and it also makes forming chords alot easier (this is important if you want to play melody over them).
Altered (open) tunings have the same advantage as capoing. They allow certain things to be played easily and they allow open strings in different keys. If you like playing closed-position ("bingo") chords, then this won't make much difference to you. But if you play open-position and partial chords up and down the neck, this might be an advantage for you.
Likewise, certain melodies are easier to play in open tuning. Since guitar players have only six strings to work with, for fast passages it's important to find places where fast passages can be played in a single position without having to slide to a new position.
Speaking personally, though, it's rare that I use open tunings. I prefer to know the location of each note on my fretboard and that means I play in standard tuning which I have more or less memorized over the years. I will capo, though, because that doesn't interfere with my fretboard knowledge, it only changes the open strings I have available.
I learn and listen to Neil Young nowadays, and he often uses Dropped D tunings and others. Yes, if your tune in D that low D gives a nice tone to the accompaniment.
But that's not the only reason.
I've got a free Android program that tells from a fingering the possible chords and in most cases is right (you can do squeeze your brain to figure out without programs but I'm too lazy). What I have noticed at Neil, that with this alternate tunings he can play jazzy (7, 9, 13 chords - minor, major) in an easy way, and they sound cool. That is why his progressions sound so interesting.