Please help to get duration between two 4 or 3 character string
-
DATEDIFF(minute, CONVERT(varchar,[begin_time],8) , CONVERT(varchar,[end_time],8)) as [duration]
this was not worked for me could you please help above 3 formats how to get duration
-
Those values do not represent time.
So, guessing what they are supposed to represent, you have to first convert them to a format that SQL can interpret as time, then do teh calculation.
DECLARE @Data table (begin_time varchar(8), end_time varchar(8));
INSERT INTO @Data ( begin_time, end_time )
VALUES ( '830', '1100' )
, ( '1200', '1600' )
, ( '0', '2359' );
SELECT begin_time = CONVERT(datetime, STUFF(RIGHT('0000' + d.begin_time, 4), 3, 0, ':') + ':00', 8)
, end_time = CONVERT(datetime, STUFF(RIGHT('0000' + d.end_time, 4), 3, 0, ':') + ':00', 8)
, [duration] = DATEDIFF(MINUTE, CONVERT(datetime, STUFF(RIGHT('0000' + d.begin_time, 4), 3, 0, ':') + ':00', 8), CONVERT(datetime, STUFF(RIGHT('0000' + d.end_time, 4), 3, 0, ':') + ':00', 8))
FROM @Data AS d; -
My version looks similar to the above!
DROP TABLE IF EXISTS #Time;
CREATE TABLE #Time (StartTime INT, EndTime INT);
INSERT #Time (StartTime, EndTime)
VALUES
(830, 1100)
,(1200, 1600)
,(0, 2359);
SELECT
calcs.StartTime
, calcs.EndTime
, Seconds = DATEDIFF(SECOND, calcs.StartTime, calcs.EndTime)
FROM #Time t
CROSS APPLY
(
SELECT
StartTime = CAST(STUFF(RIGHT(CONCAT('0000', t.StartTime), 4), 3, 0, ':') AS TIME)
, EndTime = CAST(STUFF(RIGHT(CONCAT('0000', t.EndTime), 4), 3, 0, ':') AS TIME)
) calcs;The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Here's an alternative method, just for the heck of it:
SELECT
begin_time, end_time,
LEFT(end_time, ISNULL(NULLIF(LEN(end_time) - 2, -1), 0)) * 60 -
LEFT(begin_time, ISNULL(NULLIF(LEN(begin_time) - 2, -1), 0)) * 60 +
RIGHT('0' + end_time, 2) - RIGHT('0' + begin_time, 2) AS mins_diff
FROM @DataSQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
All of our solutions come crashing down if the start and end times span midnight, of course.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - DBA.k wrote:
DATEDIFF(minute, CONVERT(varchar,[begin_time],8) , CONVERT(varchar,[end_time],8)) as [duration]
this was not worked for me could you please help above 3 formats how to get duration
What are the original datatypes in those columns? It looks to me like they may be INTs. Is that correct?
Also, you didn't say in what units you wanted the duration to be displayed in nor what format.[EDIT] Ah... never mind. I see that your attempt was to display in minutes.
--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) -
The following will work whether the source data is an INT or VARCHAR(). Of course, it has the same caveat that Phil mentioned.
SELECT end_time/100*60 + end_time%100 - begin_time/100*60 - begin_time%100
FROM #TestTable
;--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) -
A middle way could be to use the very nice integer division Jeff uses for the time parts but then use framework functions to handle type conversions and calculation. Supposedly, more framework = better self documentation but in this case it seems less readable compared to the math.
declare @Data table (begin_time varchar(8),
end_time varchar(8));
insert into @Data ( begin_time, end_time )
values ( '830', '1100' )
, ( '1200', '1600' )
, ( '0', '2359' );
select d.*, tm.*, datediff(minute, tm.begin_tm, tm.end_tm) time_diff_minutes
from @Data d
cross apply (values (timefromparts(d.begin_time/100, d.begin_time%100, 0, 0, 0),
timefromparts(d.end_time/100, d.end_time%100, 0, 0, 0))) tm(begin_tm, end_tm);begin_timeend_timebegin_tmend_tmtime_diff_minutes
830110008:30:0011:00:00150
1200160012:00:0016:00:00240
0235900:00:0023:59:001439Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Just to expand on the ideas presented by Jeff Moden and Steve Collins, here is my contribution, which also solves the midnight spanning caveat...
(and yes, I prefer a simple select over values most of the time - I'm just that old-fashioned) 🙂
declare @Data table (begin_time varchar(8),
end_time varchar(8));
insert into @Data ( begin_time, end_time )
values ( '830', '1100' )
, ( '2200', '500' ) -- Note: spans midnight!
, ( '1200', '1600' )
, ( '0', '2359' );
select
d.begin_time,
d.end_time,
tm.begin_tm,
tm.end_tm,
tdm.time_diff_minutes,
tdmx.time_diff
from @Data d
cross apply ( -- convert the varchar input values into time values...
select timefromparts(d.begin_time/100, d.begin_time%100, 0, 0, 0) as begin_tm,
timefromparts(d.end_time/100, d.end_time%100, 0, 0, 0) as end_tm
) tm
cross apply ( -- if end_tm is less than begin_tm then convert it to a datetime value and add a full day (this is only used as an intermediate value)..
select case when tm.end_tm < tm.begin_tm then dateadd(d,1,cast(tm.end_tm as datetime)) else tm.end_tm end as end_tm
) tmx
cross apply ( -- calculate the difference in minutes...
select datediff(minute, tm.begin_tm, tmx.end_tm) time_diff_minutes
) tdm
cross apply ( -- convert the calculated difference in minutes into a time value...
select cast(dateadd(minute,tdm.time_diff_minutes,0) as time(0)) as time_diff
) tdmx;begin_time end_time begin_tm end_tm time_diff_minutes time_diff
---------- -------- ---------------- ---------------- ----------------- ----------------
830 1100 08:30:00 11:00:00 150 02:30:00
2200 500 22:00:00 05:00:00 420 07:00:00
1200 1600 12:00:00 16:00:00 240 04:00:00
0 2359 00:00:00 23:59:00 1439 23:59:00
(4 rows affected) -
Just to continue, if you limit things to the original problem, solving the "spans midnight" problem is almost trivial. You just have to know the "gazintas". 😀 In this case, it's assumed that if the begin_time and end_time are identical, then 24 hours passed instead of zero time as the former seems more likely and, without a date element, the two possibilities are impossible to distinguish from each other.
Again, this works whether the inputs are INTs or character based.
--===== Create and populate the test table
DROP TABLE IF EXISTS #TestTable;
CREATE TABLE #TestTable
(begin_time VARCHAR(10), end_time VARCHAR(10))
;
INSERT INTO #TestTable WITH (TABLOCK)
(begin_time,end_time)
VALUES ( '830','1100')
,('2200', '500') -- Note: spans midnight
,('1230','1200') -- Note: spans midnight
,('2359', '0') -- Note: spans midnight
,('2359','2359') -- Note: spans midnight and same time next day
,('1200','1600')
,( '0','2359')
;
--===== Solve the problem with just a minor addition to the original code.
SELECT *
,MinutesDur = IIF(begin_time/1<end_time/1,0,1440)
+ end_time/100*60+end_time%100-begin_time/100*60-begin_time%100
FROM #TestTable
;Results:
--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) -
All nice and well,
How about:
declare @Data table (begin_time varchar(8),
end_time varchar(8));
insert into @Data ( begin_time, end_time )
VALUES ( '830','1100')
,('2200', '500') -- Note: spans midnight
,('1230','1200') -- Note: spans midnight
,('2359', '0') -- Note: spans midnight
,('2359','2359') -- Note: spans midnight and same time next day
,('1200','1600')
,( '0','2359');
with cteCorrectDateType as (
select d.*, tm.*
, DATEADD(day, DATEDIFF(day,0,getdate()), CAST(begin_tm AS DATETIME2(7))) beginDt2
, DATEADD(day, DATEDIFF(day,0,getdate()) + case when begin_tm > end_tm then 1 else 0 end, CAST(end_tm AS DATETIME2(7))) EndDt2
from @Data d
cross apply (values (timefromparts(d.begin_time/100, d.begin_time%100, 0, 0, 0),
timefromparts(d.end_time/100, d.end_time%100, 0, 0, 0))) tm(begin_tm, end_tm)
)
Select *
, datediff(MINUTE, beginDt2, EndDt2) as time_diff_minutes
from cteCorrectDateType
;I do not consider "2359 to 2359" a valid case, as that might imply it may also span multiple days
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I do not consider "2359 to 2359" a valid case, as that might imply it may also span multiple days
As could any of the other midnight-spanning entries. You have to decide what spanning midnight means. The logical choice is to assume it means that it's the next day, but we'll never know for sure.
-
Removed... near duplicate post.
--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) - Johan Bijnens wrote:
I do not consider "2359 to 2359" a valid case, as that might imply it may also span multiple days
Ordinarily, I'd agree but to consider the span of multiple days, the original data would need to include a date... and it does not. The limits of the data relegate this problem to no more than 24 hours... but not less.
If, however, 24 hours should not be considered, then just add the '=' symbol to the '<' symbol and Bob's your uncle.
--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) -
The picture you posted is not a table by definition. It's got duplicate rows! You have no key or constraints. The strings you posted are not how we represent a time value in SQL (or any other ISO standard I know) Based on nothing you posted, Did you know that SQL has a TIME(n) data type? Why did you fail to use it and go for a 1950s COBOL kludge with strings instead?
CREATE TABLE Foobar
(begin_time TIME(0) NOT NULL DEFAULT '00:00:00',
end_time TIME(0) NOT NULL PRIMARY KEY, --- keys are not optional
CHECK (begin_time < end_time),--- my guess
);
I'm going to guess you want times rounded to the minute. You can either do this in the DDL or you can do it in the presentation layer. The presentation layer will probably be more accurate.
INSERT INTO Foobar (begin_time, end_time)
VALUES ('08:30:00', '11:00:00'), ('12:00:00', '16:00:00'), ('00:00:00', '2359');
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply