CODEWITHSUNDEEP
sql-servert-sqldatetimesumvarchar
user1457104
user1457104

Reputation: 69

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!

Upvotes: 2

Views: 71

Answers (2)

PhilS
PhilS

Reputation: 624

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.

Upvotes: 1

Dan Guzman
Dan Guzman

Reputation: 46231

Below is one method, using FORMAT to facilitate formatting of your non-standard time format:

WITH downtime AS (
    SELECT
      Reason
    , Shutdowndate
    , StartupDate
    , DATEADD(millisecond, DATEDIFF(millisecond, Shutdowndate, StartupDate), '') AS DownTime
    FROM dbo.ApplicationDowntime
)
SELECT
      Reason
    , Shutdowndate
    , StartupDate
    , FORMAT(((DATEPART(day, DownTime) - 1) * 24) + DATEPART(hour, DownTime), '00:')
        + FORMAT(DownTime, 'mm:ss:fff') AS TotalDownTime
FROM downtime;

Note that this returns 189:39:01:873 for the third row of your sample results:

+-----------------------+-------------------------+-------------------------+---------------+
|        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 | 189: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:500 |
| Scheduled Maintenance | 2018-12-06 00:00:00.000 | 2018-12-18 08:41:45.007 | 296:41:45:007 |
+-----------------------+-------------------------+-------------------------+---------------+

Upvotes: 0

Related Questions