It will largely be governed by context.
"I haven't seen xxx for a while."
"Oh, didn't you know? He's in hospital after a heart attack."
An ongoing situation where xxxx is likely to be under medical care for more than a day.
"Where is xxxx today?"
"He's in the hospital today for an x-ray."
A temporary situation and a specific reference to the location of the event.
"Can I find Dr xxxx?"
"He is at the hospital today doing his clinic there."
It is his place of work.
I would say that "at hospital" is not a usage that you will hear.