When I first started wearing I was obsessed with the new feel (which is normal). I'd sleep well, but I spent a lot of time just enjoying the extra padding, rubbing my hand along my back so I could feel where the edge of my diaper met my skin, etc etc. Now, it's gotten to the point where I sleep well in them, though I don't pad up every night.
I really only wear to bed. It's something I reserve for those special days where I just get that feeling of "man I need to get diapered" (similar to the "man I need a drink" feeling =p). They positively impact my sleep, but there are so many other factors that impact my sleep (my evening tea, my plushies, weighted blanket, etc) that the effect of the diapers alone doesn't make a night and day difference. It's a great bonus on top of everything else, don't get me wrong. I do diaper up frequently, but I don't diaper up specifically because it's the one thing that helps me sleep. It's just like a really nice side dish.
One thing I will add is that if I wear a diaper to bed, I notice that I'm MUCH lazier the next day. I'll be slower to get up and get moving, etc =p.
'