Unable to update in 24 hours format

1

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 NULL,
[JobSite] nvarchar NULL,
[VendorCode] nvarchar NULL,
[Location] nvarchar NULL,
[DayWorkingStart] time NULL,
[DayWorkingEnd] time NULL,
[NightWorkingStart] time NULL,
[NightWorkingEnd] time NULL,
[TotalHoursWorked] time NULL,
[ApprovedHours] [float] NULL,
[OTHours] [float] NULL,
[Supervisor] nvarchar NULL,
[Remarks] nvarchar NULL,
[RigManager] nvarchar 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','06:00:00','18:00:00',
null,null,null,null,null,null,null,getdate(),1
)

SELECT @id=SCOPE_IDENTITY()

declare @start as varchar(10)
declare @end as varchar(10)
declare @hours as varchar(10)
declare @mins as varchar(10)

--set @start=DATEDIFF(minute,@dayendtime,@daystarttime)
--set @end=DATEDIFF(minute,@nightendtime,@nightstarttime)
--set @hours= (@start+@end)/60
--set @mins= (@start+@end)%60

declare @t1 int,@t11 int
declare @t2 int,@t12 int
declare @t3 int,@t13 int
declare @t4 int,@t14 int

declare @timehrs int
declare @timemin int
declare @time varchar(10)

select @t1 = datepart(HOUR,@dayendtime)
select @t2 = datepart(HOUR,@daystarttime)
select @t3 = datepart(HOUR,@nightendtime)
select @t4 = datepart(HOUR,@nightstarttime)
select @t11 = datepart(MINUTE,@dayendtime)
select @t12 = datepart(MINUTE,@daystarttime)
select @t13 = datepart(MINUTE,@nightendtime)
select @t14 = datepart(MINUTE,@nightstarttime)

declare @tothrs varchar(10)
set @timehrs =(@t1+@t2+@t3+@t4)
set @timemin =(@t11+@t12+@t13+@t14)

set @tothrs = cast(@timehrs as varchar)+':'+cast(@timemin as varchar)

update #tblAttendance
set
TotalHoursWorked=@tothrs
where AttendanceLogId=@id
commit transaction
end

the problem is when i am trying to update #tblAttendance.TotalHoursWorked it is not calculating in 24hrs foramt
it is storing as TotalHoursWorked = 48:00
where as i need to store 24:00

2

Hard to sort things out with the excessive use of vars that you use only once.

select @t1 = datepart(HOUR,@dayendtime)
select @t2 = datepart(HOUR,@daystarttime)
select @t3 = datepart(HOUR,@nightendtime)
select @t4 = datepart(HOUR,@nightstarttime)

Why not do a sum of this in one swoop?

select @sumhours = datepart(HOUR,@dayendtime) +
datepart(HOUR,@daystarttime) +
datepart(HOUR,@nightendtime) +
datepart(HOUR,@nightstarttime)
3

Also please provide the following variables
@dayendtime, @daystarttime, @nightendtime,@nightstarttime

Clean up stuff we wont use

--set @start=DATEDIFF(minute,@dayendtime,@daystarttime)
--set @end=DATEDIFF(minute,@nightendtime,@nightstarttime)
--set @hours= (@start+@end)/60
--set @mins= (@start+@end)%60

Everything you provide has to be testable on our side. You have to try it out first on your end on a test database then post here. We do not have tbl_HR_EmployeeAttendanceLog or is that replaced by #tblAttendance?
Otherwise there is back and forth. Remember people are volunteering to help you out from their busy schedule. Help us help you.

4

yosiasz,

thanks for replying,
you made my code lines lesser.

but my problem is when user gives input in 24 hours format ,while adding it becomes 48 hrs instead of 24 hrs. below is the example.

set @dayendtime = '06:00:00',
set @daystarttime = '18:00:00',
set @nightendtime = '06:10:00',
set @nightstarttime= '18:00:00'

output should be 24:10 instead of 48:10

else any other way how we can achieve this ?

5

i have replaced it by #tblAttendance

6

and now we need the data types for these?
declare @dayendtime money? varchar? time data type?

Clean up and repost please. Remember we can help you but cant do these type of works for you

set @dayendtime = ' **06** :00:00',
set @daystarttime = ' **18** :00:00',
set @nightendtime = ' **06** :10:00',
set @nightstarttime= ' **18** :00:00'

Obviously you have not tried this on your side and it is failing on our side
You have nvarchars but no length associated with them.

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 NULL,
[JobSite] nvarchar NULL,
[VendorCode] nvarchar NULL,
[Location] nvarchar NULL,
[DayWorkingStart] time NULL,
[DayWorkingEnd] time NULL,
[NightWorkingStart] time NULL,
[NightWorkingEnd] time NULL,
[TotalHoursWorked] time NULL,
[ApprovedHours] [float] NULL,
[OTHours] [float] NULL,
[Supervisor] nvarchar NULL,
[Remarks] nvarchar NULL,
[RigManager] nvarchar 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','06:00:00','18:00:00',
null,null,null,null,null,null,null,getdate(),1
)
7

yosiasz,

you are not understanding my problem.

my second post is link with 1st.

i have declared it nvarchar(50) but i have no idea why it is not visible in the post

datatype is time for the below variables

set @dayendtime = ' 18 :00:00',
set @daystarttime = ' 06 :00:00',
set @nightendtime = ' 18 :10:00',
set @nightstarttime= ' 06 :00:00'

8

ah gotcha , always use this

image

9

the sum of these

set @dayendtime = ' 06:00:00',
set @daystarttime = ' 18:00:00',
set @nightendtime = ' 06:10:00',
set @nightstarttime= ' 18:00:00'

is 48:100 Could you please explain to us why it should be 24:10 ?

How can start time be18:00:00 which is 2pm and endtime be 06:00:00 which is 6am?

Could you expound on that please?

10

Sorry for inconveniences I have edited by previous post. I had a typo error I stated starttime for endtime and vice versa that was incorrect.

12 is 12
1 is 13
2 is 14
,
,
6 is 18

11

no worries. So we understand that it is a 24 hour time span. What I still dont understand is why it should be 24:10?

Please take this script and fix up the errors you say you fixed on your previous post and post pack?


declare @id int;

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] time 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','06:00:00','18:00:00',
null,null,null,null,null,null,null,getdate(),1
)

SELECT @id=SCOPE_IDENTITY()

declare @start as varchar(10)
declare @end as varchar(10)
declare @hours as varchar(10)
declare @mins as varchar(10)


declare @t1 int,@t11 int
declare @t2 int,@t12 int
declare @t3 int,@t13 int
declare @t4 int,@t14 int

declare @timehrs int
declare @timemin int
declare @time varchar(10)

declare @dayendtime time = '06:00:00',
        @daystarttime time = '18:00:00',
        @nightendtime time = '06:10:00',
        @nightstarttime time= '18:00:00'


select datepart(HOUR,@dayendtime) as dayendtime,
		datepart(HOUR,@daystarttime) as daystarttime,
		datepart(HOUR,@nightendtime) as nightendtime,
		datepart(HOUR,@nightstarttime) as nightstarttime

declare @sumhours int = datepart(HOUR,@dayendtime) +
                        datepart(HOUR,@daystarttime) +
                        datepart(HOUR,@nightendtime) +
                        datepart(HOUR,@nightstarttime)

select @sumhours as sumhours
declare @summinutes int = datepart(MINUTE,@dayendtime) +
datepart(MINUTE,@daystarttime) + 
datepart(MINUTE,@nightendtime) + 
datepart(MINUTE,@nightstarttime)

select @summinutes as summinutes

declare @tothrs varchar(10)

set @tothrs = cast(@sumhours as varchar)+':'+cast(@summinutes as varchar)
select @tothrs

drop table #tblAttendance
12

Ok,
6am to 18pm in the sense 6am to 6pm in 12hrs format right

6+18+6+18 = 24 I m considering 18 as 6

13

ok now I understand !

14

the query you posted @sumhours will return 48:10

I am expecting 24:10

15

the query I posted was as a sample script for you to use so you fix up the issues you posted. it was not meant as a solution.

Just hold on until a solution is provided please

16

I fixed up what you have sent.
do you have solution for my question ?

17

wait

18

I believe the issue was that you had [TotalHoursWorked] as time but I believe it should be varchar(5)

use sqlteam
go

IF OBJECT_ID('tempdb..#tblAttendance') IS NOT NULL 
    DROP TABLE #tblAttendance




declare @id int;

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','06:10:00','18:00:00',
null,null,null,null,null,null,null,getdate(),1
)

SELECT @id=SCOPE_IDENTITY()

declare @start as varchar(10)
declare @end as varchar(10)
declare @hours as varchar(10)
declare @mins as varchar(10)


declare @t1 int,@t11 int
declare @t2 int,@t12 int
declare @t3 int,@t13 int
declare @t4 int,@t14 int

declare @timehrs int
declare @timemin int
declare @time varchar(10)

declare @daystarttime  time = '06:00:00',
        @dayendtime time = '18:00:00',
        @nightstarttime  time = '06:10:00',
        @nightendtime time= '18: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

 select * from #tblAttendance
19

Thank you

20

Hi all,

for the below input the above code is returning incorrect value @nightstarttime = 18:00 is nothing but 6.00 pm in 12 hrs format to till @nightendtime = 2:00 am.

declare @daystarttime time = '06:00:00',
@dayendtime time = '18:00:00',
@nightstarttime time = '19:00:00',
@nightendtime time= '02:00:00'

am getting result in negative could you please help me out to resolve the issue ?