You have an overnight job which is triggered by the arrival of some external data. You keep a record of the date and time that the overnight job is run. Someone wants to know what the average start time of the job is. You can’t use the average function on dates and times in T-SQL so you need to write some custom code to calculate the average time the job starts.
First, you need to identify the point in time that you want the calculation to consider the start of a new day. For a job which runs during the day could be 0 – indicating midnight. For an overnight job you would probably want to consider the possibility that the job could be started the next morning. In that situation you might choose 18 – indicating 6pm as the start of a new day.
Then, in the calculation itself:
- identify the number of seconds since the day division
- get the average of number of seconds
- add to a date time and extract the time portion