Actually I have only done it with 1) my iPhone and 2) a new Canon S95. I haven't yet done it with my DSLR. There are a number of software products out there now with varying degrees of sophistication but I haven't jumped on one yet.
I have used it with my iPhone camera and it is pretty amazing. As you well know film and digital sensors are not as sensitive as the human eye so have much less dynamic range. It is interesting to see photos with and without the HDR activated. With HDR looks almost surreal if you are used to standard photography but on the other hand the HDR photos look much more like what we actually see. You definitely see more in the shadows and the highlights are not burned out.
I just got the Canon S95 and have only played with the HDR once. It is best done with a tripod but it works. The HDR is built in to the camera and simply takes a few shots then processes them into one. I'm sure all the digital SLRs will have it built in soon too.
The reason I brought it up is that sunsets and sunrises are exactly the type of photo scene that would benefit from creative use of HDR. I always miss seeing the lovely foreground colors and features when the sky is exposed to maximize drama and color so HDR helps a lot in that setting.