March 28, 2021 at 12:36 pm
I've got a T-SQL statement which I use for comparing the duration of an SSIS package against previous executions (to see if they're slowing down) but it doesn't work if the duration is longer than 24 hours. The problematic SQL is:
-- THIS DOES NOT WORK
DECLARE
@today [varchar](12) = '44:10:27.766', -- OVER ONE DAY
@yesterday [varchar](12) = '02:59:40.266'
SELECT
RIGHT(CONVERT([varchar], DATEADD(MILLISECOND, DATEDIFF_BIG(MILLISECOND, @yesterday, @today), 0), 121), 12) AS [delta]
Msg 241, Level 16, State 1, Line 5
Conversion failed when converting date and/or time from character string.
-- BUT THIS DOES
DECLARE
@today [varchar](12) = '23:10:27.766', -- UNDER ONE DAY
@yesterday [varchar](12) = '02:59:40.266'
SELECT
RIGHT(CONVERT([varchar], DATEADD(MILLISECOND, DATEDIFF_BIG(MILLISECOND, @yesterday, @today), 0), 121), 12) AS [delta]
delta
20:10:47.500
Is there a way to do this which would support hours greater than 23?
March 28, 2021 at 3:49 pm
this will work as long as difference does not go past 31 days
SELECt RIGHT(CONVERT(varchar(19) -- always put the size when defining datatype - do not use defaults as those can change and break code
, DATEADD(MILLISECOND, DATEDIFF_BIG(MILLISECOND, yesterday, today), 0)
, 121)
, 11) AS [delta]
from (select dateadd(hour, convert(int, left(@today, 2)), convert(datetime, stuff(@today, 1, 2, '00'))) as today
, dateadd(hour, convert(int, left(@yesterday, 2)), convert(datetime, stuff(@yesterday, 1, 2, '00'))) as yesterday
) t2
March 28, 2021 at 5:48 pm
Thanks for that. There was a slight issue in that the day portion being returned was 2nd of the month for a date duration of 1 day - because 0 is the 1st of the month. However your solution gave me the pointer I needed to develop this (I've added a bit of formatting as well).
SELECT
CASE DAY(DATEADD(MILLISECOND, DATEDIFF_BIG(MILLISECOND, yesterday, today), 0))
WHEN 1 THEN ''
ELSE RIGHT('0' + CONVERT([varchar](2), DAY(DATEADD(MILLISECOND, DATEDIFF_BIG(MILLISECOND, yesterday, today), 0)) - 1), 2) + ':'
END + RIGHT(CONVERT([varchar](23), DATEADD(MILLISECOND, DATEDIFF_BIG(MILLISECOND, yesterday, today), 0), 121), 12) AS [delta]
FROM
(
SELECT
DATEADD(HOUR, CONVERT([int], LEFT(@today, 2)), CONVERT(DATETIME, STUFF(@today, 1, 2, '00'))) AS [today],
DATEADD(HOUR, CONVERT([int], LEFT(@yesterday, 2)), CONVERT(DATETIME, STUFF(@yesterday, 1, 2, '00'))) AS [yesterday]
) AS [converted]
March 28, 2021 at 6:35 pm
Please see the following article for calculating durations. It's really simple to do when you understand the hidden powers of the DATETIME datatype. Heh... you may never use DATEDIFF() to calculate duration ever again. 😀
EDIT: My apologies. I didn't realize that you were only dealing with time instead of dates and times.
--Jeff Moden
March 28, 2021 at 9:36 pm
modified version that solves that particular issue - previous one was really just addressing the parsing, not the accuracy of the output.
this version also addresses possibility of more than 99 hours duration as well as yesterday's duration being longer than todays - on that case a negative sign is added to the difference
select t3.diffsign + convert(varchar(5), days_diff) + ':' + timepart as days_hours_min_sec
, t3.diffsign + convert(varchar(5), hours_diff) + ':' + right(timepart, 9) as hours_min_sec
from (select dateadd(hour, convert(int, left(@today, charindex(':', @today) -1 )), convert(datetime, stuff(@today, 1, charindex(':', @today) -1, '00'))) as today
, dateadd(hour, convert(int, left(@yesterday, charindex(':', @yesterday) -1 )), convert(datetime, stuff(@yesterday, 1, charindex(':', @yesterday) -1, '00'))) as yesterday
) t2
outer apply (select case
when datediff_big(millisecond, yesterday, today) > 0
then dateadd(millisecond, datediff_big(millisecond, yesterday, today), 0)
else dateadd(millisecond, datediff_big(millisecond, today, yesterday), 0)
end as datediff
, case
when datediff_big(millisecond, yesterday, today) > 0
then ''
else '-'
end as diffsign
) t3
outer apply (select datediff(day, 0, datediff) as days_diff
, datediff(hour, 0, datediff) as hours_diff
, right(convert(varchar(23), datediff, 121), 12) as timepart
) t
March 28, 2021 at 10:47 pm
A framework-ish way could be to use the awkward DATETIMEFROMPARTS and its 7 required parameters.
declare
@today [varchar](12) = '44:10:27.766', -- OVER ONE DAY
-- @today [varchar](12) = '23:10:27.766',
@yesterday [varchar](12) = '02:59:40.266';
declare
@diff_ms bigint =
(select datediff_big(ms, datetimefromparts(1753, 1, 1+left(@yesterday, 2)/24, left(@yesterday, 2)%24,
substring(@yesterday, 4, 2), substring(@yesterday, 7, 2),
substring(@yesterday, 10, 3)),
datetimefromparts(1753, 1, 1+left(@today, 2)/24, left(@today, 2)%24,
substring(@today, 4, 2), substring(@today, 7, 2),
substring(@today, 10, 3))));
select @diff_ms milliseconds_difference,
@diff_ms/(1000*60*60*24) dy,
@diff_ms/(1000*60*60)%24 hr,
@diff_ms/(1000*60)%60 mi,
(@diff_ms/1000)%(60*60*24)%60 sec,
cast(((@diff_ms%(1000*60*60))%(1000*60))%1000/1000.0 as decimal(4, 3)) remainder;
milliseconds_difference dy hr mi sec remainder
148247500 1 17 10 47 0.500
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
March 28, 2021 at 10:59 pm
The script from frederico seems to be too complicated.
Here is much simpler version.
For ease of understanding I put it into sequential steps:
DECLARE
@today [varchar](12) = '44:10:27.766',
@yesterday [varchar](12) = '02:59:40.266'
select charindex(':', @today)
select substring(@today, 1, charindex(':', @today)-1)
-- Converting @today into a proper datetime value
select dateadd(dd, Hrs/24, 0) + convert(datetime,str(Hrs%24, 2)+substring(@today, charindex(':', @today),12)) EndTime
FROM (
select convert(int, substring(@today, 1, charindex(':', @today)-1)) Hrs
) H
-- Simply subtract start time from end time to find Duration:
select EndTime - convert(datetime,@yesterday) Duration
FROM (
select dateadd(dd, Hrs/24, 0) + convert(datetime,str(Hrs%24, 2)+substring(@today, charindex(':', @today),12)) EndTime
FROM (
select convert(int, substring(@today, 1, charindex(':', @today)-1)) Hrs
) H
) T
_____________
Code for TallyGenerator
March 29, 2021 at 2:56 am
Thought I'd toss my hat into the ring. Details are where they should be... in the code. 😀 It also tolerates leading, embedded, and trailing spaces, which is a frequent problem that people build into times for some reason.
CREATE OR ALTER FUNCTION dbo.DeltaDur
/**********************************************************************************************************************
Purpose:
Given a low cume duration and a high cume duration, calculate and display the difference as a duration.
The function returns the difference in duration in two different format. Please see the "Programmer's Notes" below.
-----------------------------------------------------------------------------------------------------------------------
Example Usage:
--===== Single Value
SELECT DeltaT, DeltaDT FROM dbo.DeltaDur(@LoDur,@HiDur) --Variables contain VARCHAR() durations as above.
;
--===== Table Values
SELECT dd.DeltaT, dd.DeltaDT, <<st.OtherColumns>>
FROM <<SomeQualifiedTableName>> st
CROSS APPLY dbo.DeltaDur(st.<<LoDurColumnName>>,st.<<HiDurColumnName>>) dd
;
-----------------------------------------------------------------------------------------------------------------------
Programmer's Notes:
Inputs: 1. Almost any valid clock-like input of a minimum H:MI thru HHHHHHHH:MI:SS.mmm is acceptable.
2. The maximum input is '71003135:59:59.997', which is the entire span from the first instant of
1900-01-01 'till the last DATETIME instant of 9999-12-31.
3. The smallest duration must be the first parameter (@pLoDur) and the largest duration must be the
2nd parameter.
4. NULLs will be returned if either input parameter is NULL.
5. An error will be returned if either parameter is less than a minimum of H:MI.
6. The input parameters are generally tolerant of leading, embedded, and trailing spaces up to a
maximum of 128 total characters.
Output: DeltaT
1. A clock-like output in the form of h:mi:ss.mmm where "h" may be hours up to 8 characters.
2. No left padding is done for the output so the output is NOT right justified (but could easily be).
3. The maximum output for DeltaT is '71003135:59:59.997', which is the entire span from the first
instant of 1900-01-01 'till the last DATETIME instant of 9999-12-31.
4. Return datatype for DeltaT is VARCHAR(18).
DeltaDT
1. A clock-like output in the form of dD:hh:mi:ss.mmm where "d" may be days up to 7 characters and
the "D" is a string literal.
2. No left padding is done for the output so the output is NOT right justified (but could easily be).
5. The maximum output for DeltaDT is '2958463D:23:59:59:997', which is the entire span from the first
instant of 1900-01-01 'till the last DATETIME instant of 9999-12-31.
4. Return datatype for DeltaDT is VARCHAR(21).
Resolution: Same as DATETIME (3.3ms)
Function Type: High performance iTVF (inline Table Valued Function)
1: This function uses the ISO compliant methods of being able to do simple, direct date math afforded
by the functionality of the DATETIME datatype.
-----------------------------------------------------------------------------------------------------------------------
Revision History:
Rev 00 - 28 Mar 2021 - Jeff Moden
- Initial creation.
REF: https://qa.sqlservercentral.com/forums/topic/datediff-for-times-over-a-day-long
**********************************************************************************************************************/
--===== Function Parameters
(
@pLoDur VARCHAR(128)
,@pHiDur VARCHAR(128)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
cteRawDur AS
(--==== Convert the inputs to DATETIME and subtact the low from the high.
SELECT RawDur =
(DATEADD(hh,SUBSTRING(r.HiDur,1,p.HiPos-1)+0,0) + ('00'+SUBSTRING(r.HiDur,p.HiPos,18)))
- (DATEADD(hh,SUBSTRING(r.LoDur,1,p.LoPos-1)+0,0) + ('00'+SUBSTRING(r.LoDur,p.LoPos,18)))
FROM (VALUES(REPLACE(@pLoDur,' ',''),REPLACE(@pHiDur,' ','')))r(LoDur,HiDur) --Remove any and all spaces.
CROSS APPLY(VALUES (CHARINDEX(':',r.LoDur), CHARINDEX(':',r.HiDur)))p(LoPos,HiPos) --Find the first colon.
)--==== Format the output as described in the flower box.
SELECT DeltaT = CONVERT(VARCHAR(8),DATEDIFF(hh,0,RawDur)) + RIGHT(CONVERT(CHAR(12),RawDur,114),10)
,DeltaDT = CONVERT(VARCHAR(7),DATEDIFF(dd,0,RawDur)) + 'D:' + CONVERT(CHAR(12),RawDur,114)
FROM cteRawDur
;
GO
--Jeff Moden
March 29, 2021 at 3:32 am
As a bit of a sidebar, Sergiy posted while I was working on mine. That's about the same kind of simplicity that I built into mine because you can, as he points out, subtract one whole DATETIME datatype from another to get the duration (span of time) between the two.
However, I strongly recommend not using the STR() function. It's not as bad as the FORMAT() function (which is 43 times slower than CONVERT(), but it is about 3 times slow than other methods. Please see the following old article... doing the same test on my much more modern laptop, I come up with the following performance results...
https://qa.sqlservercentral.com/articles/hidden-formatting-troubles-with-str-sql-spackle
Here are the results from just a couple of minutes ago.
(1000000 rows affected)
========== Cast, Concatenate, and Size ==========
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 203 ms, elapsed time = 197 ms.
========== The STR() Method is SLOWER ==========
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 625 ms, elapsed time = 618 ms.
--Jeff Moden
March 29, 2021 at 4:47 am
Don't worry about ms, just use seconds. A few ms diff could be caused by almost anything is and not significant for an SSIS package.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
March 29, 2021 at 5:17 am
Don't worry about ms, just use seconds. A few ms diff could be caused by almost anything is and not significant for an SSIS package.
I have to strongly disagree with that. We're not talking about a "few" ms... we're talking about something that's 3 times slower. If you call something only once a minute, you won't thing much of it. That's how a whole lot of systems with overworked CPU's and I/O systems begin... and it's difficult to fix because there's the "Death of a Thousand Cuts" everywhere. Just imagine if all the little junk all ran 3 times faster.
And why would anyone intentionally use anything that's guaranteed to be 3 times slower when the faster methods are just as easy or easier.
Like Granny used to say, "If you mind the pennies, the dollars will take care of themselves.
--Jeff Moden
March 29, 2021 at 7:03 am
@Sergiy - for sake of consistency can you give your version (condensed) that gives both outputs I gave - and taking in consideration that either today or yesterday could be more than 24 hours.
and in a single select
Oh, common, it's not that hard.
Just repeat everything what's applied to @today with @yesterday:
select StartTime, EndTime, EndTime - StartTime Duration
FROM (
select dateadd(dd, HrsT/24, 0) + convert(datetime,convert(varchar(2), HrsT%24)+substring(@today, charindex(':', @today),12)) EndTime
, dateadd(dd, HrsY/24, 0) + convert(datetime,convert(varchar(2), HrsY%24)+substring(@yesterday, charindex(':', @yesterday),12)) StartTime
FROM (
select convert(int, substring(@today, 1, charindex(':', @today)-1)) HrsT
, convert(int, substring(@yesterday, 1, charindex(':', @yesterday)-1)) HrsY
) H
) T
And the derived table [T] is added here only for the sake of explanation. You can subtract the times straight away, make the code even simpler:
select (dateadd(dd, HrsT/24, 0) + convert(datetime,convert(varchar(2), HrsT%24)+substring(@today, charindex(':', @today),12)) ) -
(dateadd(dd, HrsY/24, 0) + convert(datetime,convert(varchar(2), HrsY%24)+substring(@yesterday, charindex(':', @yesterday),12)) ) Duration
FROM (
select convert(int, substring(@today, 1, charindex(':', @today)-1)) HrsT
, convert(int, substring(@yesterday, 1, charindex(':', @yesterday)-1)) HrsY
) H
_____________
Code for TallyGenerator
March 29, 2021 at 8:32 am
I
ScottPletcher wrote:Don't worry about ms, just use seconds. A few ms diff could be caused by almost anything is and not significant for an SSIS package.
I have to strongly disagree with that. We're not talking about a "few" ms... we're talking about something that's 3 times slower.
I think Scott meant that there is no point to measure duration of an SSIS package execution with ms precision.
It sometimes takes more than a second to simply start it, before executing its first step. And the cause of such a delay would be outside of the package.
_____________
Code for TallyGenerator
March 29, 2021 at 1:47 pm
I
Jeff Moden wrote:ScottPletcher wrote:Don't worry about ms, just use seconds. A few ms diff could be caused by almost anything is and not significant for an SSIS package.
I have to strongly disagree with that. We're not talking about a "few" ms... we're talking about something that's 3 times slower.
I think Scott meant that there is no point to measure duration of an SSIS package execution with ms precision.
It sometimes takes more than a second to simply start it, before executing its first step. And the cause of such a delay would be outside of the package.
Exactly. I was talking about the original post (I would have quoted if I were talking about a later one). Going from ms to second gives you 1000 * the previous range of ~1 day, thus 1000 days, should easily cover all differences in SSIS run time.
Although, frankly I would still not worry about anything less than a 1 second diff -- and realistically much more of a diff than that --for any SSIS package.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply