Gosh, I came across this thread looking for exactly this very thing.. having the alarm go on when my Android device is off. It worked perfectly years ago on my super old Nokia whatever-it-was (and define "off" as you prefer
Point is, as it was said, even with PCs you can set them to boot at a specific time and do what you prefer, so why couldn't an almighty Android (which I love) device do as much?
I don't understand why so many of you guys keep saying it's stupid/useless to switch off the device by night; whatever you do, this is a personal thing, not an answer to the original question..
Now waiting for some good-hearted guy to write such an app, maybe there is a little workaround to this situation.
The thing you need, is your device being booted before the alarm has to go off, right? There are apps which do exactly this. They (re)boot the device at times that you can set. Using one of these and setting the boot-up, say, 5-10 minutes before the alarm should do the trick.
Certainly you have now to deal with 2 apps for just setting a stupid alarm, but I'm positive at some point an app which combines these two things will be ready. Plus, if you wake up always between say 7-9am, you could anyway set the boot up at 6:50am and forget about it. The alarm will work also if the device prompts for PIN code (well, it does on my Samsung Galaxy Nexus).
I saw PowerBoot should be a right candidate, but it requires root access to the device, which I don't have yet. Anyway I don't see why this shouldn't work.