We have taken overnight train journeys a number of times mostly from Paris to Venice or Rome. They are hardly awesome, but can be interesting if you like train travel. They can also be noisy and uncomfortable particularly because even the first class sleepers are small and cramped.
But if you can sleep, and I find I generally can, you will wake up in a new city and to a new adventure.

I did London to Cologne in day time and then got overnight train to Copenhagen. Spent the day at Copenhagen saw Little Mermaid strolled around and went to Tivoli Gardens.That evening got an overnight train to Stockholm and spent the day there. In the evening got the night ferry to Helsinki.......from there I was heading overland to Japan and went to St Petersburg for Moscow for Trans Manchurian to Beijing where I then caught train to Qingdao for ferry to Japan.

Thank you RJD. Perhaps "awesome" was too enthusiastic," but I do do pretty well with them, even in the dreaded 6 bunk configuration! I will add PAris to Venice and Paris to Rome to my list.

And yes, that's what I like, waking up in a new city, ready to go!

In a couple of weeks we are taking an overnight from Lisbon to San Sebastian - we have been to Spain several times but never to SS so we think this should be a great trip.

Several years ago we went from Amsterdam to Basel overnight and then went on to Montreux. Have also gone Rome to Montreux, I don't think we had to change trains that time. I'm thinking about a Budapest to Venice trip one of these years - what a great combination of cities!

I think there is alot of info and possibilities at the man in seat 61? web site? Hopefully someone will post a link.

I've done a rail trip from Stockholm to Barcelona and as part of that did overnight rail segments between Berlin and Zurich and Zurich to Barcelona. I've also done the overnights between Paris and Madrid (twice); Barcelona and Milan; Paris and Copenhagen; Milan to Amsterdam (no longer operating as I recall.

Of those, the Barcelona-Milan or Barcelona Zurich route was interesting since these trains are the same between Barcelona and Lyon where they split. The views of the Med between Barcelona and Sete are great BUT you have to be ont correct side of the train and unless you do it in "high" summer it gets to dark to see much by the time you reach Sete on the coast.

The Milan-Amsterdam route runs along the Rhine eventually and did so in basic morning until mid-day daylight.

As to the rail accommodation and the overall experience, I've always traveled in First or in "Gran Classe" (between Barcelona-Milan and Paris-Madrid so I had all the creature comforts including a private shower, the air conditioning worked, the ride was smooth, and there was little disruptive noise.

I would NOT advise you to take any of these trips if you are mainly interested in scenery, however. It can be pretty but it is far too fleeting. IMO you Do these runs either because you love trains (as I do) or because it is the most convenient way to get between Points A+B.

We took an overnight train from Rome to Turin. While not fantastic, it certainly fulfilled fhe need. I don't think my son slept much. Lol! We kept our luggage at the train station and right before we boarded the train, we changed into our "pajamas". It was pretty funny. I don't know if flannel bottoms are exactly the fashion statement I wanted to make in Rome but I was comfortable. We changed again when we arrived in Turin.

Certainly an experience and I am glad we did it. Another thing to laugh about now.

Back in my student days, I did Paris-Florence twice and Milan-Paris once as overnights, always in the six-person couchettes. Fine, and as you say, the best part is waking up in a new city (albeit a bit bleary-eyed!).

More recently, I took an overnight train Amsterdam-Berlin. That seemed a great use of time! And my bf and I got a 2-person sleeper, which was very much of an improvement over sharing sleeping quarters with strangers (even if it wasn't in reality as romantic as it looks in movies LOL).

My subjective upsides and downsides:

Upsides

Save costs for one night at hotel.
Well, not 100pct as rail fares are not necessarily cheaper than airfares. But usually you do save something. If you were fair you'd have to compare it against the costs for a cheap hostel to make available comfort comparable. Or to get comparable comfort on a night train you pay quite dearly for first class doubles. Even more for single occupancy, to make it comparable to your hotel costs (assuming that you usually don't sleep in 6 bed dorms at hostels).

Adventure.
Well, not quite as taking a kayak up the Amazon. Night trains run across Europe day in day out. Night in night out, I guess. But still a different way to travel.


Downsides

Your timing.
In spring you will leave when it's already dark and arrive when it's still dark or just dawning. You won't get any scenery at that time of year on many legs.

Available sleeping time.
When a train is set to leave at 10pm you probably won't be dreaming in your couchette at 10.05pm. You will also have to get up (obviously) some time before the train arrives. Some overnight trains have quite short travel times, so you should deduct maybe half an hour after departure and one hour before arrival to see if the remaining time is what you call a good night's sleep.
It won't make much difference if you got a flight leaving at 10pm instead and were at your hotel at 12.30am. Many overnights travel on routes that planes can do in 1-1.5hrs (like Berlin-Zurich, Zurich-Barcelona, Amsterdam-Berlin).

Logistics upon arrival.
It may be fun to start the day in Paris a 7am in bright sunshine in July with the first boulangeries open and sipping a coffee on the steps of Sacre Coeur before any tourists show up. It will be slightly different in spring when it's still dark outside.

Necessary travel time.
This should be an upside, but most reports on airport hassles are either correct for certain notoriously overcrowded hubs like Heathrow or Paris-CDG, or are computed from enormous comfort zones or experiences with extra-slow security checkpoints.
For example, when I fly from Munich to Barcelona (pretty much the same as Zurich-BCN), I would leave my apartment in central Munich 2hrs before departure. Walk to suburban train, ride to airport. Arrive 1hr before departure. Check-in luggage or not. Security is 0-5 minutes wait. 15mins during rush hours. No passport control. Fly 80-90 minutes. Baggage claim, leave airport appr. 30mins after arrival at gate. Take cab to downtown hotel, 30mins max. Total travel time door-to-door: 4.5 to 5 hours. So time does add up, but it's a far cry from the horror stories.
Extra costs for airport train and taxi: €30-35.

Luggage.
Even if you arrived at the hotel by plane at midnight, you'd have your room, bed, and closets to settle down.
When you arrive by overnight train, you won't get your room until around 2pm. Not that much of an inconvenience, I would say.

In summary I would say that taking an overnight can be something different, and a bit exciting, and may even save a few euros. But if you were a night train rookie I would only do it once and not base my whole vacation on this means of transportation.

Is sleep important to you?
It is to me and I did the overnite only once--never again.