What is a good way to query data stored in multiple identical tables split by date/time
Personally, I'd consider using triggers to load a proper single table and query that.
Then you have control of partitioning (if needed), defining good indexes etc

Depending on volumes, you could use service broker to decouple the app and your tables.

Basically, anything to avoid massive UNIONs and dynamic SQL...