I'm getting in a tangle with doing time comparisons from SQL server job history, and was wondering if there's a simple, elegant way of converting/combining times?
The field [run_duration] in msdb..sysjobhistory is an integer in the form of hhmmss rather than a UNIX-like count of the number of seconds elapsed. I'm sure this was a great way to save space when HDDs were measured in $ per MB, but strikes me as archaic when my telephone is more powerful than my desktop computer of a decade back. Perhaps SS2008 uses the TIME data type, but I'm still on SS2005.
It does mean that an attempt to get the average duration of a job using AVG() produces a figure that is no longer hhmmss. Since I also want to add the average to the start time of the job to get an anticipated completion time, so it would make sense to work in time and used DATEADD to deal with the modulo 60 arithmetic.
I feel that my monstrous constructs with string conversions to change the run_duration & job times into seconds are not the way to go (my keen sense of shame prevents me from posting them here, lest I be banished); can one of you take them out behind the woodshed and humanely end their suffering?
Much obliged,
lex
soi là, soi carré
The field [run_duration] in msdb..sysjobhistory is an integer in the form of hhmmss rather than a UNIX-like count of the number of seconds elapsed. I'm sure this was a great way to save space when HDDs were measured in $ per MB, but strikes me as archaic when my telephone is more powerful than my desktop computer of a decade back. Perhaps SS2008 uses the TIME data type, but I'm still on SS2005.
It does mean that an attempt to get the average duration of a job using AVG() produces a figure that is no longer hhmmss. Since I also want to add the average to the start time of the job to get an anticipated completion time, so it would make sense to work in time and used DATEADD to deal with the modulo 60 arithmetic.
I feel that my monstrous constructs with string conversions to change the run_duration & job times into seconds are not the way to go (my keen sense of shame prevents me from posting them here, lest I be banished); can one of you take them out behind the woodshed and humanely end their suffering?
Much obliged,
lex
soi là, soi carré
