Your instinct are dead-on. Students, at least in grades k-12, are not terrifically motivated by what they will "need" in the working world. There are plenty of good reasons for this. First of all, what they will do for their work seems very abstract and far-off.
Also, viewed from a vocational lens, there is a quite reasonable shot that they won't actually need the things we think they will. They might not be going into (or might not think they are going into) the careers we are thinking of, and in any case, jobs are so heterogeneous that to make almost almost any statement of what work requires nearly meaningless. Add into the mix the speed at which the world seems to be changing, and our predictive abilities just get worse and worse.
But not everything is lost. People, including kids, are intrinsically motivated to learn when conditions are right. But we have to get a lot of different elements all moving in the same direction, and for some students, this is easier to manage than for others. Some students love school and think of it as a place where they can thrive, but some others are a little broken and burnt out, or feel unsafe either about approaching difficult topics, or about their surroundings, and these problems can make intrinsic motivation very hard to tap into.
The end-goal is to help kids feel safe to try their best, that the ends are actually achievable, and that the material connects to their current world.
-- Organizing the world for motivation --
Let's start with a few ideas that have to come from the front of the room. Certain teacher attitudes permeate the classroom. These attitudes are not sufficient by themselves to motivate kids, but if they're not there, nothing else is going to go right.
The teacher believes in the material being taught. They think that it is both useful and interesting. (And hopefully a little amazing, too.)
The teacher believes that the students are absolutely capable of learning the material.
The teacher loves their students, and genuinely wants to see those students thrive.
The teacher praises good student practices above and beyond student accomplishments. There is a lot of research to back this up; students who receive praise predominantly for being right, for being smart, or for being the best at things often become wary of difficult material and of the unknown. Why throw yourself into something if you can't succeed like you have in the past?
By contrast, if you are typically praised for good process or for working hard, then when the material gets hard, that praise still feels quite accessible. You can still organize your own learning and work hard.
There are a few things that the students need to know, and these should be directly taught and modeled at every opportunity.
They are safe in school. There are people who care about them, there are people who are their friends, and there are people that they can go to if they are down or get into some sort of trouble. (Particularly in the k-12 arena, this requires some active monitoring on the part of the teacher, and may necessitate some intervention as well. People don't thrive when they are frightened or lonely.)
Intelligence is not fixed. Intelligence is more like a muscle, because you can flex it and make it more powerful over time with work. Improvement is always the goal, not just the end-product.
Once the beliefs above are in place, we can actually do the work of motivating the students:
What makes something intrinsically interesting is connections. The more connections that can be drawn from the material to the world that the students already know about, the more intrinsically interesting the material is.
Those connections can be to the students' social worlds (that's where you end up with entertainment tie-ins; if the kid knows that "all the other kids" think that Beyoncé is cool, then class material that relates to Beyoncé is now instantly connected to that child's social world.)
Of course, it's better if the connections are deeper and more thoughtful, but if you're working with kids who are difficult to motivate, take no shame in working any angle available to you.
The more the connections, the better. The richer the connections, the better. The more surprising the connections, the better. Connect ideas to what they've seen in the past. Connect ideas to weird things you've just shown them. Connect ideas to the world around them. Take pains here to make the invisible absolutely explicit.
Help students to monitor their own progress and set their own short-term goals that lead to the big picture that the students can actually accomplish. (This is hard to do. Even adults are often bad at assessing their own understanding, so teachers can create activities to help kids reliably monitor their own learning.)
Keep your criticisms of students both constructive and loving. Always make explicit how your criticisms are really aimed at helping the student, NOT cutting them down, and make clear that there will be accompanying support to help the student learn to do better.
It is achievable
It is. It's not easy, but it's possible to get kids really excited about learning all sorts of things. If you watch some videos of amazing teachers (YouTube is an incredible resource here), you can watch this motivating work taking place at every grade level and in every sort of classroom imaginable.
Learning to apply these ideas to your classroom is hard. Watch an amazing third-grade music teacher, and then watch an amazing 11th-grade math teacher, and their practices will seem very different at first glance, but the core underlying ideas are actually the same.