Appttime add with duration how to get endtime please

  • apptime+duration

     

    Hoe to get appt_time+duration = end_time can you please help on this

  • I would do something like

    SELECT appt_time + duration as end_time

    BUT that assumes that the datatypes on those 2 columns can be added together.  For example, if they are CHARs, that's not going to add well.  You will likely need to put in some logic too so you can ensure that if the right 2 numbers of appt_time are 60 or greater that you increment the left 2 numbers by 1 and subtract 60 from the right 2.

    OR a bunch of casts:

    SELECT
    CAST(LEFT(REPLACE(CAST(DATEADD(MINUTE,duration,
    CAST(LEFT(appt_time, LEN(appt_time) - 2) + ':' + RIGHT(appt_time, 2) AS TIME)
    )
    AS VARCHAR(25)),':',''),4) AS INT);

    Basically, we are converting appt_time to a TIME datatype so we can do a DATEADD on it and once we do that, we are stripping out the : between the hours and minutes and converting back to an INT.  Messy but it works!

     

  • The math could look something like this

    declare @data table (app_time varchar(8), 
    end_time varchar(8),
    duration varchar(8));

    insert into @data(app_time, end_time, duration) values
    ('1330', null, '30'),
    ('1430', null, '30'),
    ('1030', null, '20'),
    ('800', null, '15'),
    ('300', null, '10');

    select d.*, (app_time/100+(app_time%100+duration)/60)*100+
    (app_time%100+duration)%60 calc_end_time
    from @data d
    app_time	end_time	duration	calc_end_time
    1330 NULL 30 1400
    1430 NULL 30 1500
    1030 NULL 20 1050
    800 NULL 15 815
    300 NULL 10 310

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Duplicate q.  See q "Please help to get duration between two 4 or 3 character string" in this same thread.

    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."

  • This was removed by the editor as SPAM

  • You really should take a timeout, step back and seriously consider changing the structure of that table.

    If you change the app_time and end_time into columns of type time(0), this would allow you to do real/normal time calculations using functions that work on date and time values, i.e. DateAdd, DateDiff, DateParts etc. (even though the function names could lead you to believe that they only are relevant for dates, that's not the case - they work on time values as well).

    It will make your life that much easier in the long run.

    • This reply was modified 1 month, 1 week ago by  kaj.

Viewing 6 posts - 1 through 6 (of 6 total)

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