Search code examples
sql-servert-sqldatetimesumvarchar

How can I sum time values (stored as varchars) that exceed the normal varchar value?

I am having trouble with a script I am working on. What I have are values in a database for when an application is turned off, when it is turned back on, and the total duration the application has been turned off. With the script I am using now, sometimes the TotalDownTime exceeds 24 hours. This is intentional and I would like to keep it this way. However, I would also like to be able to Sum all of the TotalDownTimes together into one value based on the reason these applications were shut down. How can this best be accomplished?

Here are some examples I am using:

Reason                  Shutdowndate            StartupDate             TotalDownTime
Scheduled Maintenance   2018-12-10 09:31:47.317 2018-12-10 11:31:47.317 02:00:00:000
Scheduled Maintenance   2018-12-10 09:38:00.373 2018-12-10 09:45:38.613 00:07:38:240
Scheduled Maintenance   2018-12-10 10:43:01.000 2018-12-18 08:22:02.873 21:39:01:873
Scheduled Maintenance   2018-12-16 00:01:07.697 2018-12-16 12:00:10.953 11:59:03:257
Scheduled Maintenance   2018-12-01 00:00:00.000 2018-12-18 13:54:16.500 421:54:16:000
Scheduled Maintenance   2018-12-06 00:00:00.000 2018-12-18 08:41:45.007 296:41:45

Here is what I am using to assign the TotalDownTime value:

Update ProductionShutdownRecord 
set TotalDownTime = CAST(DATEDIFF(HOUR, [ShutdownDate], [Startupdate]) AS VARCHAR)
     + RIGHT(CONVERT(CHAR(8),DATEADD(SECOND,DATEDIFF(SECOND, [ShutdownDate], [Startupdate]),0),114),6)
where shutdownId = 18

And here is what I am TRYING to use to sum the values, the error I get is "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. ":

select convert(char(8),dateadd(second,SUM ( DATEPART(hh,(convert(datetime,TotalDownTime,1))) * 3600 +
DATEPART(mi, (convert(datetime,TotalDownTime,1))) * 60 + DATEPART(ss,(convert(datetime,TotalDownTime,1)))),0),108)
FROM ProductionShutdownRecord
where Reason like 'Scheduled Maintenance%'
and ShutdownDate >= '01/01/2018'
and ShutdownDate <= '01/01/2019'
and startupdate is not null

Any help is appreciated, thank you!

Solution

  • What data types are the fields in your table?

    I would treat TotalDownTime as an INT or BIGINT with the difference being stored in multiples of the lowest time part required. e.g. DATEDIFF(second, Startupdate, Shutdowndate) or DATEDIFF_BIG(second, Startupdate, Shutdowndate). Then SUM is trivial.

    It then becomes an output formatting issue to get it in whatever string format you want which also should be straight forward - if you need the format available directly in the database add a calculated field to the table.