Home Dashboard Directory Help
Search

Change datatype in sysjobshistory by FatherJack


Status: 

Closed


17
0
Sign in
to vote
Type: Suggestion
ID: 687492
Opened: 9/7/2011 3:17:53 AM
Access Restriction: Public
0
Workaround(s)
view

Description

The sysjobhistory table in the MSDB database has several columns that are INT datatype that are used to store date based values. The columns are specifically

run_date
run_time
run_duration

As SQL Server now has DATE and TIME data types the values in these columns would be more readily usable if their data types changed to DATE, TIME and TIME respectively. Aggregation and calculation using functions such as SUM, AVERAGE, DATEADD, DATEPART, DATEDIFF would be applicable to the column value without any prior CAST/CONVERT or 'shredding' operations to split the hours, minutes and seconds values out. Using an INT to store a time in the HHMMSS format makes problems when the data comes to be used.

This change would benefit SQL Server by reducing developer time required and unnecessary processing of the values to carry out calculations including but not limited to those described above.
Details
Sign in to post a comment.
Posted by Microsoft on 6/13/2013 at 11:51 AM
Hello FatherJack and Rob,

Thank you for suggesting this change. Unfortunately, I would like to let you know that we won't be able to take it into coming SQL Server release. We might reconsider it for one of the future releases.

Thank you again for the feedback. We rely on customers' input a lot in improving our products and appreciate it a lot.

Evgeny Krivosheev
SQL Server Program Manager
Posted by FatherJack on 7/12/2012 at 1:32 AM
I agree wholly with Rob that the run_duration would be best as an Interval data type or in the interim as an integer to hold the count of seconds of the duration, I was not thinking it through properly when I created this item.

Thanks for the contribution Rob.
Posted by Rob Volk1 on 7/11/2012 at 2:52 PM
I would caution against using TIME for run_duration as it will overflow if a job runs for 24 hours or longer. I believe certain replication jobs already run for such durations. Using int for seconds of duration would suffice. If SQL Server next version introduces an Interval data type (e.g. http://www.postgresql.org/docs/9.1/static/datatype-datetime.html) then that would be ideal.
Posted by Microsoft on 9/9/2011 at 9:52 AM
Hello

Thank you for proposing new DCR we always looking forward to improve our product based on the customer feedback.

We will consider this feature as a part of our planning for the next major release.

Thank you for your help
Alex Grach [MSFT]
Sign in to post a workaround.