Hi Everyone,

Its been awhile since I posted and I am having a real brain buster 3 days straight and still no solution

I am trying to get values(which are based in seconds) for MTD the problem is some start and end dates will overlap

MTD (October)

ex; StartDate 9-22-2016 EndDate 10-03-2016

browsing countless pages I found this sample to calculate the time between overlapping days

**max(0, min(EndDate1, EndDate2) - max(StartDate1, StartDate2))**

problem is T-SQL max/min function only takes 1 parameter

so I created 2 UDF max and min to take 2 parameters

## example MAX UDF

function dbo.UDFexample(@x datetime, @y datetime)

returns datetime

as

begin

return case when @x >@y then @y else @x end

end

function dbo.UDFexample(@x datetime, @y datetime)

returns datetime

as

begin

return case when @x >@y then @y else @x end

end

The UDF_FORMAT_TO_DATE function just converts the seconds to "HH:MM:SS" format

```
```SELECT

EXP.[LNG_EQUIPMENT_ID] 'LNG_EQUIPMENT_ID'

,EXP.[STR_EQUIPMENT_ID] 'STR_EQUIPMENT_ID'

,EXP.STR_EQUIPMENT_LABEL 'EQUIPMENT_LABEL'

```
,DBO.UDF_FORMAT_TO_DATE(SUM(datediff(SECOND, DBO.MAT24965_MAX(0, DBO.MAT24965_MIN(GETDATE(), EXP.[STR_ENDTIME_UTC])) -
DBO.MAT24965_MAX(EXP.STR_STARTTIME_UTC, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()),0)), '01-01-2016'))) AS MTD
,DBO.UDF_FORMAT_TO_DATE(SUM(datediff(SECOND, DBO.MAT24965_MAX(0, DBO.MAT24965_MIN(GETDATE(), EXP.[STR_ENDTIME_UTC])) -
DBO.MAT24965_MAX(EXP.STR_STARTTIME_UTC, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()),0)), '01-01-2016'))) AS YTD
,DBO.UDF_FORMAT_TO_DATE(SUM(datediff(SECOND, DBO.MAT24965_MAX(0, DBO.MAT24965_MIN('10-09-2016', EXP.[STR_ENDTIME_UTC])) -
DBO.MAT24965_MAX(EXP.STR_STARTTIME_UTC, '09-20-2016'), '01-01-2016')))
AS STARTTOENDTOTAL
```

FROM [dbo].[EXPtimes] EXP

WHERE

AND DATEADD(dd, DATEDIFF(dd, 0, EXP.STR_STARTTIME_UTC), 0) >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()),0)

AND DATEADD(dd, DATEDIFF(dd, 0, EXP.STR_ENDTIME_UTC), 0) <= GETDATE()

GROUP BY

EXP.LNG_EQUIPMENT_ID,

EXP.STR_EQUIPMENT_ID,

EXP.STR_EQUIPMENT_LABEL

I am getting an error

"the datedff function resulted in an overflow. the number of dateparts separating two/datetime instances is too large"

Please let me know if you need more detail

To whoever replies

Thanks in advance you will save me a ton of stress and hair

--

Digi