Please help to get duration between two 4 or 3 character string

  • duration

     

    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;

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • 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 @Data

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • All of our solutions come crashing down if the start and end times span midnight, of course.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • DBA.k wrote:

    duration

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    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_time	end_time	begin_tm	end_tm		time_diff_minutes
    830 1100 08:30:00 11:00:00 150
    1200 1600 12:00:00 16:00:00 240
    0 2359 00:00:00 23:59:00 1439

    Aus 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    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


    ;

    2021-02-25 11_28_42-Testen met TIMEFROMPARTS.sql - UABE0DV34.uabeprd.com_GNKD002.master (ADAGILITY_j

    I do not consider "2359 to 2359" a valid case, as that might imply it may also span multiple days

    • This reply was modified 6 days, 13 hours ago by  Johan Bijnens.

    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.

    • This reply was modified 6 days, 12 hours ago by  kaj. Reason: Color coded quote
  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    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 27 total)

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