March 3, 2021 at 12:16 pm
Hoe to get appt_time+duration = end_time can you please help on this
March 3, 2021 at 2:03 pm
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!
March 3, 2021 at 2:40 pm
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
March 3, 2021 at 3:51 pm
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."
March 4, 2021 at 6:54 am
This was removed by the editor as SPAM
March 5, 2021 at 12:14 pm
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.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply