Should I be concerned if I need to have job that starts at 10pm and won't complete by the end of the production day (23:59)? How will this MSSQL job be affected by the start of the new production schedule and compile @ (00:00)?
It won't affect your compile at all. In reality you should actually compile your schedule several days in advance. We compile 5 days in the future. Make sure you review your options if you want a second instance to start if the prior instance is still running. You can find this in the "options" tab of the job definition.
Since the job always starts at 10pm, it will continue to run until completion, even if it completes after midnight. As Richard says, compilation doesn't affect running jobs.
Check your System Configuration in the Activities menu. What do you have the "Days to Carryover" set to (found at the bottom of the Master tab)? If you have this set to 0, then the job may remain in active status after it completes. The master does not seem to update the job status of jobs that run longer than the number of carryover days. This would only be a issue if your MSSQL job has a Successor dependency.