No matter when you exercise, it's almost always beneficial.

Provided you're getting enough sleep, feel good before and after your workouts, and have enough energy to perform at your best, anytime timeframe is fine. Having missed runs earlier in the day, I have gone running outdoors after midnight to make it up. On weekends, in the early morning hours (when subway service has stopped in my city) I frequently walk several miles home. I also weight train early in the morning or late at night depending on my schedule.

However training during off peak times (like overnight) limits the gyms you can use, restricts access to workout partners, trainers and coaches, and in some cases (especially if you're training outside) could pose a safety risk. Also, if you're training for competition, it might be better to train during times when they most frequently occur. For example, if they mostly take place in the early evening, you want to be used to exercising and condition your body to be at peak performance at that time. Elite marathon runners do this when they arrive in the city hosting the race days or weeks ahead of time to familiarize themselves with the course.

Regardless of the time you exercise, to be successful, I believe you have to find a time when you can consistently exercise several times a week. This is more important than working out during "normal hours" if you end up missing a lot of workouts. Also, studies have shown workout time does not have much impact on sleep schedules. For example, working out late at night will not prevent you from sleeping overnight.