Appttime add with duration how to get endtime please

Question

Post reply

Appttime add with duration how to get endtime please

DBA.k

Ten Centuries

Points: 1113
More actions
March 3, 2021 at 12:16 pm

#3852816

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

March 4, 2021 at 6:54 am

This was removed by the editor as SPAM

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

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

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

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 above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Steve Collins SSCrazy Points: 2157 More actions · Answer 2

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_timeend_timedurationcalc_end_time
1330NULL301400
1430NULL301500
1030NULL201050
800NULL15815
300NULL10310

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

ScottPletcher SSC Guru Points: 101093 More actions · Answer 3

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

kaj SSCommitted Points: 1605 More actions · Answer 4

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 3 years, 8 months ago by kaj.