• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1130
  • Last Modified:

Difference of two datetime columns caused overflow at runtime.

Got a strange one here experts...

I have a sp to work out the average between 2 datetimes, and then group them by another field...

(See below)

CREATE PROCEDURE [dbo].[Helpdesk_rep16_average_ClosureTime_by_SLA]
/*@start nvarchar(25),
   @end nvarchar(25)*/
AS
SELECT     datediff(minute, Call.LogDatTim, RepSLT.FixDate) AS Diff_in_mins, Call.LogDatTim,
     RepSLT.FixDate, Call.CallNumber, RepSLT.SLT,
     MAX(convert(VARCHAR(10), dateadd(SECOND, SLT_Totals.TotalFixTime / SLT_Totals.CountFixes, '1900-01-01'), 108))
FROM         RepSLT INNER JOIN
                      Call ON RepSLT.CallNumber = Call.CallNumber INNER JOIN
                      (SELECT SLT2.SLT, sum(
                       CASE WHEN Call2.LogDatTim = 0 OR SLT2.FixDate = 0 THEN 0 ELSE
            datediff(SECOND, Call2.LogDatTim, SLT2.FixDate) END) AS TotalFixTime,
                              count(*) AS CountFixes
                       FROM RepSLT SLT2 INNER JOIN
                            Call Call2 ON SLT2.CallNumber = Call2.CallNumber
                       GROUP BY SLT2.SLT) AS SLT_Totals ON SLT_Totals.SLT = RepSLT.SLT
WHERE (NOT (FixDate = CONVERT(DATETIME, '1899-12-30 00:00:00', 102))) and  (NOT (FixDate IS NULL))
GROUP BY datediff(minute, Call.LogDatTim, RepSLT.FixDate), Call.LogDatTim, RepSLT.FixDate, Call.CallNumber, RepSLT.SLT
ORDER BY RepSLT.SLT

However when I try to run it I get Difference of two datetime columns caused overflow at runtime?
On line 10 which is....


SELECT     datediff(minute, Call.LogDatTim, RepSLT.FixDate) AS Diff_in_mins, Call.LogDatTim,


I have tried just running the line on its own and it works fine????

Anyone know how too sort this, please show me by developing it in the above sp...

0
paulo111
Asked:
paulo111
1 Solution
 
BillAn1Commented:
it's probably the datediff(second....) that is giving you a problem. datediff returns integer, so it will only work for dates that are within 2^31 (max integer) seconds, which works out to be about 68 years.
0
 
paulo111Author Commented:
No problem, seconds for my averages arent essential... Can someone show me (syntax from the sp above required please) how to do the average for just  hours/mins

Regards
0
 
Brian CroweDatabase AdministratorCommented:
CREATE PROCEDURE [dbo].[Helpdesk_rep16_average_ClosureTime_by_SLA]
/*@start nvarchar(25),
   @end nvarchar(25)*/
AS
SELECT DATEDIFF(minute, Call.LogDatTim, RepSLT.FixDate) AS Diff_in_mins,
     Call.LogDatTim,
     RepSLT.FixDate,
     Call.CallNumber,
     RepSLT.SLT,
     MAX(convert(VARCHAR(10), DATEADD(minute, SLT_Totals.TotalFixTime / SLT_Totals.CountFixes, '1900-01-01'), 108))
FROM RepSLT
INNER JOIN Call
     ON RepSLT.CallNumber = Call.CallNumber
INNER JOIN (SELECT SLT2.SLT,
          SUM(CASE
               WHEN Call2.LogDatTim = 0 OR SLT2.FixDate = 0 THEN 0
               ELSE DATEDIFF(minute, Call2.LogDatTim, SLT2.FixDate)
          END) AS TotalFixTime,
          Count(*) AS CountFixes
     FROM RepSLT SLT2
     INNER JOIN Call Call2
          ON SLT2.CallNumber = Call2.CallNumber
     GROUP BY SLT2.SLT) AS SLT_Totals
     ON SLT_Totals.SLT = RepSLT.SLT
WHERE FixDate <> CONVERT(DATETIME, '1899-12-30 00:00:00', 102) and NOT FixDate IS NULL
GROUP BY DATEDIFF(minute, Call.LogDatTim, RepSLT.FixDate), Call.LogDatTim, RepSLT.FixDate, Call.CallNumber, RepSLT.SLT
ORDER BY RepSLT.SLT
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now