As for coffee: I never drink much at night, unless it's after some nice dinner. The effects are impossible to tell, because usually there's some wine involved as well, and that's a rouser after a couple of hours.
On the very rare occasion that I drink an espresso at night without alcohol with it; there's not much to say about the effects beforehand. Sometimes I just sleep like a baby, sometime I lie awake all night. I think it's very interesting what kingseven says: that the cafeine blocks the receptors for the hormone that calms you down. That might explain a lot
During the day, I find that cafeine doesn't keep me awake at all: I get very tired from it, actually! It might give a bit of a rush for a couple of minutes, but that might as well be the hot drink. If I have to get up too early, after too little sleep, it doesn't get me going at all.
If you really want something to keep you awake, try chocolate in any form. A hot cocoa during the night is a guarantee for no sleep at all.
