DATEDIFF() for times over a day long

Question

DATEDIFF() for times over a day long

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

I still concern myself with such things because it doesn't make sense to use something that's known to be slower when there's something easy you can do to avoid the slowness. And, no, it won't make much of a difference in a single proc or package. It does, however, make a difference "in practice". If you get in the habit of doing everything the right/best way for performance, then you can seriously help affect the "Death by a thousand cuts" performance issues that collectively make for a very sad machine.

But, whatever. If you want to use something that's a known performance issue, you're not working on my servers and so you can go for it. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Jeff Moden wrote:

Thought I'd toss my hat into the ring. Details are where they should be... in the code. 😀

Call me old -fashioned, but I prefer functions to perform a single function. And the one you posted combines 3: parsing text to datetime, finding out the duration and formatting the output.

So I've come up with a simple function which converts HHH:MM:SS.MS string to datetime value:

CREATE FUNCTION dbo.HHHMMSS2DATETIME ( 
         @HHHMMSS VARCHAR(128)
        )
RETURNS TABLE AS
 RETURN 
SELECT dateadd(dd, Hrs/24, 0) + convert(datetime,
convert(varchar(2), Hrs%24)
+CASE WHEN charindex(':', substring(@HHHMMSS, charindex(':', @HHHMMSS)+1, 3)) = 3 
 THEN substring(@HHHMMSS, charindex(':', @HHHMMSS),12)
 ELSE ':' + @HHHMMSS 
 END ) DTM
FROM (
select CASE WHEN charindex(':', substring(@HHHMMSS, charindex(':', @HHHMMSS)+1, 3)) = 3
AND LTRIM(substring(@HHHMMSS, 1, charindex(':', @HHHMMSS)-1) ) NOT LIKE '%[^0-9]%'
THEN convert(int, substring(@HHHMMSS, 1, charindex(':', @HHHMMSS)-1)) 
ELSE 0 END Hrs
) H
/*
SELECT * FROM dbo.HHHMMSS2DATETIME('44:10:27.766')
SELECT * FROM dbo.HHHMMSS2DATETIME('44:10.766')
*/

It has does some input validation as well, but it's surely not a complete set. It trims the input string, but does not remove spaces from within the input value, as it might be not the right thing to do.

It also takes care of situations when HHH part is omitted completely.

Anyway, once the conversion is done, you may chose any way you like to find the duration.

It may be simply deduct StartTime from End Time:

SELECT T.DTM - Y.DTM Duration
FROM dbo.HHHMMSS2DATETIME ('44:10:27.766') T,dbo.HHHMMSS2DATETIME('02:59:40.266')Y

Or you may use my AgeCalculation function (posted on this site):

select * from dbo.HHHMMSS2DATETIME ('1244:10:27.766') T
Cross join dbo.HHHMMSS2DATETIME('02:59:40.266') Y
CROSS APPLY dbo.AgeCalculation (Y.DTM, T.DTM, DEFAULT)

Some may prefer DATEDIFF (UGGGHHH!)

And the final formatting of the output - oh, there are so many way and so many different requirements for that - no point to even discuss it.

The point is - with 3 tasks separated you have flexibility to use them as they fit requirements, or not to use any of them, if there are no requirements for that.

This reply was modified 3 years, 7 months ago by Sergiy.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

The problem is, that's not a function. That's a script. Convert it to a function while I'm writing a similar one. 😀

And you seem to be saying that 'SELECT * FROM dbo.HHHMMSS2DATETIME('XXXX:10:27.766')' should return a proper date/time. Why wouldn't you consider the "XXXX" to be an error?

Also, thanks for the samples in the block comments. In the same vein as why someone would think of returning a value with "XXXX" in it, it also allowed me to consider that my code won't return an error if something like '44:10.766' is use (no hours). In this particular case, I'd consider that to be an error and it other cases, perhaps not. Either way, my code silently produces the incorrect answer instead of failing or providing the correct answer. I've got to work on that.

As for replacing the spaces, that would be easy to remove but I included it because I've seen such things happen in telephony data many times.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Well, dbo.HHHMMSS2DATETIME is a function.

But the overall solution is a script, true. But I don’t see it as a problem, it’s a feature! 😀

I don’t know how the duration is meant to be measured, what’s the format of the outcome. Even if the formatting is actually required.

As for "XXXX" to be an error - I don’t know. Validating the data formatting according to the specified rules is another function.

I could make it fail on such entry, or return NULL, or treat it like an empty space, zero hours. I picked the last option, but honestly - I did not think about it for too long. Anyway - with no rules defined we are free to make up our own ones..

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

I agree. Validating dates and times from possibly free-form sources is a real pita, especially in the presence of only "implied" specs. The OP never stated if the inputs always had at least hours and minutes for the inputs. Most of us may have made a mistake in assuming that in the absence of anything specific.

Shifting gears a bit, I was just a bit disappointed because you obviously have a function. How difficult would it have been to simply post it as a function? 😀

On the old fashioned stuff of having 3 functions as you said, I frequently do the same (I'm also a bit "old fashioned" but I'm also loath to have functions call functions with the one possible exception of calling a Tally function. If I have to do something frequently, I'll write a specific function to solve the issue instead of having to string many functions together in many places where I need the same functionality. While I may have a function that converts a duration to a date/time, it's not difficult to write a similar conversion into a function that does more.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Jeff Moden wrote:

Shifting gears a bit, I was just a bit disappointed because you obviously have a function. How difficult would it have been to simply post it as a function? 😀

OMG. I could not figure out what are you talking about, and then I noticed that the header fell off the copy-paste carriage somehow.

I fixed the script - it's now a function indeed.

As for doing the same thing repeatedly - when it takes multiple steps it's usually called "procedure", and yes, it makes perfect sense to store it for future reuse. Luckily, somebody thought about it before me, so for repeated processes I use that feature named "stored procedures". 🙂

As for function on function - we use it all the time. Look at your script on the previous page:

CONVERT(.. DATEDIFF(... DATEADD( .. SUBSTRING( ... REPLACE(...)))))

Well, those are not user-defined, but still functions.

User-defined objects (not only functions) imply some limitations, but you must be quite off to go beyond 256 nested function calls. Either system or UDF.

But I don't see anything wrong about applying some string formatting function on the output of dbo.AgeCalculation (dbo.HHHMMSS2DATETIME(....)), with AgeCalculation using TallyGenerator multiple times. Works pretty well, even on an older laptop.

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 7

Just remembered a thing about insufficiently specified requirements.

A wife sends her husband to a grocery shop:

- Buy a loaf of bread and if there will be eggs buy a dozen.

20 minutes later husband returns home with a dozen of bread loafs.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

Sergiy wrote:

Just remembered a thing about insufficiently specified requirements.
A wife sends her husband to a grocery shop:
- Buy a loaf of bread and if there will be eggs buy a dozen.
20 minutes later husband returns home with a dozen of bread loafs.

Nice... that's a great example and, sad to say, is too often true in requirements for code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)