Where do you see negative values?
Hi yosiasz,
When I try to update table #tblAttendance
declare @daystarttime time = '06:00:00',
@dayendtime time = '18:00:00',
@nightstarttime time = '19:00:00',
@nightendtime time= '02:00:00'
declare @sumhours int = DATEDIFF(hour,@daystarttime,@dayendtime) + DATEDIFF(hour,@nightstarttime,@nightendtime)
declare @summinutes int = (DATEDIFF(minute,@daystarttime,@dayendtime) + DATEDIFF(minute,@nightstarttime,@nightendtime)) % 60
declare @tothrs varchar(5)
set @tothrs = cast(@sumhours as varchar(50) )+':'+cast(@summinutes as varchar(50) )
update #tblAttendance
set TotalHoursWorked=@tothrs
I am not seeing any negative values? 
Since @nightstarttime = 19 pm that is 7 o clock in 24hrs format and @nightendtime = 2 am when we take difference of @nightstarttime and @nightendtime that is 17 which is wrong it should be 7 hrs instead.
well since now you are spanning days we will need to do something different. No way for the query to know what you mean by '02:00:00' with no date specified. give me some time 
get it ? time? never mind
Sure 
so totalhours worked should be 19 hours correct?
--Day Time
6am to 6pm = 12 hours
--Night TIme
7pm to 2am = 7
Total is 19 hours?
declare @daystarttime datetime = '1900-01-01 06:00:00', --6 am
@dayendtime datetime = '1900-01-01 18:00:00', --6 pm
@nightstarttime datetime = '1900-01-01 19:00:00', --7pm
@nightendtime datetime= '1900-01-02 02:00:00' --2am
declare @sumhours int
declare @summinutes int
select @sumhours = (sum(TotalSeconds) % 86400) / 3600 ,
@summinutes = (sum(TotalSeconds) % 3600) / 60
from
(
select 1 as eventId, DateDiff(second, @daystarttime, @dayendtime) as TotalSeconds union
select 2 as eventId, DateDiff(second, @nightstarttime, @nightendtime) as TotalSeconds
) x
declare @tothrs varchar(5)
set @tothrs = cast(@sumhours as varchar(50) )+
':'+cast(@summinutes as varchar(50) )
select @tothrs;
update #tblAttendance
set TotalHoursWorked=@tothrs
select * from #tblAttendance
Total is 24hrs
Above is just an example to explain
huh? the person in your example worked 24 hours?!?
This for our client, actually they have two shifts
did the last solution I provided fix your issue?
No I have not tried am not at my computer I definitely try and let you know
Hi yosiasz,
i have checked the above code since datetime is hardcode so i have commented it and tried running calculated Totolhoursworked is '-5' instead 19
value it was returning correct since we are declared @nightendtime datetime= '1900-01-02 03:00:00'
but when by default we are converting date part will 1900-01-01 need some changes in code
CREATE TABLE #tblAttendance
(
[AttendanceLogId] [int] IDENTITY(1,1) NOT NULL,
[FK_ServiceTypeId] [int] NOT NULL,
[FK_ClientId] [int] NULL,
[FK_EmpMasterId] [int] NOT NULL,
[VehicleCode] nvarchar(150)NULL,
[JobSite] nvarchar(150)NULL,
[VendorCode] nvarchar(150)NULL,
[Location] nvarchar(150)NULL,
[DayWorkingStart] time NULL,
[DayWorkingEnd] time NULL,
[NightWorkingStart] time NULL,
[NightWorkingEnd] time NULL,
[TotalHoursWorked] varchar(5) NULL,
[ApprovedHours] [float] NULL,
[OTHours] [float] NULL,
[Supervisor] nvarchar(150)NULL,
[Remarks] nvarchar(150)NULL,
[RigManager] nvarchar(150)NULL,
[AttendanceDate] [date] NULL,
[CreatedDate] [datetime] NULL,
[ModifiedDate] [datetime] NULL,
[StatusFlag] [int] NULL,
)
insert into #tblAttendance
(FK_ServiceTypeId,FK_ClientId,FK_EmpMasterId,VehicleCode,JobSite,VendorCode,[Location],DayWorkingStart,DayWorkingEnd,NightWorkingStart,NightWorkingEnd,TotalHoursWorked,
ApprovedHours,OTHours,Supervisor,Remarks,RigManager,AttendanceDate,CreatedDate,StatusFlag)
values
(1 ,1,1,'1001','Sp676','LIV234',null,'06:00:00','18:00:00','19:00:00','02:00:00',
null,null,null,null,null,null,null,getdate(),1
)
--declare @daystarttime datetime = '1900-01-01 06:00:00', --6 am
--@dayendtime datetime = '1900-01-01 18:00:00', --6 pm
--@nightstarttime datetime = '1900-01-01 19:00:00', --7pm
--@nightendtime datetime= '1900-01-02 03:00:00' --2am
declare @ds datetime
declare @de datetime
declare @ns datetime
declare @ne datetime
select @ds = cast(DayWorkingStart as datetime) from #tblAttendance
select @de = cast(DayWorkingEnd as datetime) from #tblAttendance
select @ns = cast(NightWorkingStart as datetime) from #tblAttendance
select @ne = cast(NightWorkingEnd as datetime) from #tblAttendance
select @ds as '@ds',@de as '@de' ,@ns,@ne
declare @daystarttime datetime = @ds --6 am
declare @dayendtime datetime = @de --6 pm
declare @nightstarttime datetime = @ns --7pm
declare @nightendtime datetime= @ne
--SELECT @id=SCOPE_IDENTITY()
declare @sumhours int
declare @summinutes int
select @sumhours = (sum(TotalSeconds) % 86400) / 3600 ,
@summinutes = (sum(TotalSeconds) % 3600) / 60
from
(
select 1 as eventId, DateDiff(second, @daystarttime, @dayendtime) as TotalSeconds union
select 2 as eventId, DateDiff(second, @nightstarttime, @nightendtime) as TotalSeconds
) x
declare @tothrs varchar(5)
set @tothrs = cast(@sumhours as varchar(50) )+
':'+cast(@summinutes as varchar(50) )
select @tothrs;
update #tblAttendance
set TotalHoursWorked=@tothrs
select * from #tblAttendance
DROP TABLE #tblAttendance
^^^^^
2am is the next day not the same day on 1900-01-01, 2 am is on 1900-01-02
As of this writing, today in Seattle, WA USA it is 3/10/2020 7.26 AM the next 2:00 AM is going to be on 3/11/2020. The way you have it, on same date 1900-01-01 of course you will get negative values
You'll need to change your datatype to be DateTime and include the date. 
not necessarily. when doing calculation dev can use 1900-01-** as base
https://www.sqlteam.com/articles/working-with-time-spans-and-durations-in-sql-server