# Time doesn't appear in proper format

### Question

• Successfully calculated TotalTime a person has worked and overtime he has done if total working time is greater than 08:00 but if not greater than it puts duration of time person has not worked in Overtime column BUT NOW overtime column should display that time in  proper negative format e.g. if person has worked from 14:49 to 15:20 hours than Overtime column should show -00:29 but my column is displaying it like this 23:29, why 23 ?

CODE

```with times as (
SELECT    t1.EmplID
, t3.EmplName
, min(t1.RecTime) AS InTime
, max(t2.RecTime) AS [TimeOut]
, cast(min(t1.RecTime) as datetime) AS InTimeSub
, cast(max(t2.RecTime) as datetime) AS TimeOutSub
, t1.RecDate AS [DateVisited]
FROM  AtdRecord t1
INNER JOIN
AtdRecord t2
ON    t1.EmplID = t2.EmplID
AND   t1.RecDate = t2.RecDate
AND   t1.RecTime < t2.RecTime
inner join
HrEmployee t3
ON    t3.EmplID = t1.EmplID
group by
t1.EmplID
, t3.EmplName
, t1.RecDate
)
SELECT EmplID
,EmplName
,InTime
,[TimeOut]
,[DateVisited]
,convert(char(5),cast([TimeOutSub] - InTimeSub as time), 108) totaltime
,convert(char(5), case when TimeOutSub - InTimeSub >= '08:01' then
cast(TimeOutSub - dateadd(hour, 8, InTimeSub) as time) else cast(8 - (TimeOutSub - InTimeSub) as time) end, 108) as overtime
FROM times ```

• Edited by Thursday, January 23, 2014 10:27 AM
• Moved by Thursday, January 23, 2014 11:56 AM Coming from SQL Server Data Access : related to the createion of TRANSACT-SQL statements
Thursday, January 23, 2014 10:03 AM

### All replies

• Issue: I have successfully calculated TotalTime a person has worked and overtime he has done if total working time is greater than 08:00 but if not greater than it puts 00:00 in Overtime for that employee BUT NOW I DON'T WANT THIS, I WANT THAT IF PERSON HAS WORKED LESS THAN 8 hours than overtime column should display that time in negative e.g. if person has worked 7:00 hours than Overtime column should show -1 (in proper hours format)

CODE:

```with times as (
SELECT    t1.EmplID
, t3.EmplName
, min(t1.RecTime) AS InTime
, max(t2.RecTime) AS [TimeOut]
, cast(min(t1.RecTime) as datetime) AS InTimeSub
, cast(max(t2.RecTime) as datetime) AS TimeOutSub
, t1.RecDate AS [DateVisited]
FROM  AtdRecord t1
INNER JOIN
AtdRecord t2
ON    t1.EmplID = t2.EmplID
AND   t1.RecDate = t2.RecDate
AND   t1.RecTime < t2.RecTime
inner join
HrEmployee t3
ON    t3.EmplID = t1.EmplID
group by
t1.EmplID
, t3.EmplName
, t1.RecDate
)
SELECT EmplID
,EmplName
,InTime
,[TimeOut]
,[DateVisited]
,convert(char(5),cast([TimeOutSub] - InTimeSub as time), 108) totaltime
,convert(char(5), case when TimeOutSub - InTimeSub >= '08:01' then
cast(TimeOutSub - dateadd(hour, 8, InTimeSub) as time) else '00:00' end, 108) as overtime
FROM times```

Thursday, January 23, 2014 7:07 AM
• Hello ?

Thursday, January 23, 2014 7:56 AM
• ```else cast(datediff(hh,timeoutsub,dateadd(hour, 8, InTimeSub)) as time) end, 108) as overtime

```

try this as a replacement for else part ?? (sorry couldnt test)

above part will give you the time ->convert it to varchar->prefix a '-'

logic is:

(outtime-(intime+8))*-1

Thanks,
Jay

Thursday, January 23, 2014 8:15 AM
• As i understand what you need to do is just to remove the last case when and make it like this

```with times as (
SELECT    t1.EmplID
, t3.EmplName
, min(t1.RecTime) AS InTime
, max(t2.RecTime) AS [TimeOut]
, cast(min(t1.RecTime) as datetime) AS InTimeSub
, cast(max(t2.RecTime) as datetime) AS TimeOutSub
, t1.RecDate AS [DateVisited]
FROM  AtdRecord t1
INNER JOIN
AtdRecord t2
ON    t1.EmplID = t2.EmplID
AND   t1.RecDate = t2.RecDate
AND   t1.RecTime < t2.RecTime
inner join
HrEmployee t3
ON    t3.EmplID = t1.EmplID
group by
t1.EmplID
, t3.EmplName
, t1.RecDate
)
SELECT EmplID
,EmplName
,InTime
,[TimeOut]
,[DateVisited]
,convert(char(5),cast([TimeOutSub] - InTimeSub as time), 108) totaltime
,convert(char(5),
cast(TimeOutSub - dateadd(hour, 8, InTimeSub) as time) , 108) as overtime
FROM times```

Thursday, January 23, 2014 8:16 AM
• As I am not sure of your table structure and data, will not be able to suggest anything in your code.

However, you can have a look at the below snippet as reference.

```create Table T1(TimeoutSub time, TimeInSub time)

Insert into T1 Select '18:00','08:00'
Insert into T1 Select '18:00','11:00'

Select *,
Case when DATEDIFF(HOUR,Cast(Dateadd(hour,8,TimeInSub) as time),TimeoutSub)>0 Then
Else 0 End as 'OverTime',
Case when DATEDIFF(HOUR,Cast(Dateadd(hour,8,TimeInSub) as time),TimeoutSub)<0 Then
Else 0 End as 'ShortTime'
From T1

Drop table T1```

Thursday, January 23, 2014 9:23 AM
• ```now it shows negative sign with value but values are wrong, e.g. -23:29
or -18:01 for a person whose total time is 05:59
actually it shows that second value correctly after : but first is false, e.g. -23:29 should be -00:29 , -18:01 should be -00:01```

Thursday, January 23, 2014 9:42 AM

• ,convert(char(5),cast([TimeOutSub] - InTimeSub as time), 108) totaltime
,convert(char(5), case when TimeOutSub - InTimeSub >= '08:01' then
cast(TimeOutSub - dateadd(hour, 8, InTimeSub) as time) else cast(8 - (TimeOutSub - InTimeSub) as time) end, 108) as overtime
FROM times

Could you remove the cast to time and see the output?

Satheesh
My Blog

Thursday, January 23, 2014 10:28 AM
• same result
• Edited by Thursday, January 23, 2014 10:42 AM
Thursday, January 23, 2014 10:41 AM
• A Simple example
```declare @person table
(empid int, swipein datetime, swipeout datetime) ;
insert into @person values
with cte as (select empid,swipein,swipeout,datediff(minute,swipein,swipeout) Diffminutes from @person	)
select *,case when 60*8>Diffminutes then '-' + convert(varchar(10),abs(Diffminutes-60*8) /60 )+':'+convert(varchar(10),abs(Diffminutes-60*8) %60)
when 60*8<=Diffminutes then convert(varchar(10),abs(Diffminutes-60*8) /60 )+':'+convert(varchar(10),abs(Diffminutes-60*8) %60)
end from cte```

Satheesh
My Blog

Thursday, January 23, 2014 10:50 AM
• great but how to correct my code sir ?

Thursday, January 23, 2014 11:30 AM
• Hello ,

This thread is not related to SQL Server Data Access , so I am moving it towards a more suitable forum where I hope it will be more quickly answered ( and where it will be more useful and interesting for other persons meeting this kind of problems ).

Have a nice day

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Thursday, January 23, 2014 11:53 AM

Thursday, January 23, 2014 12:05 PM
• try this, not tested
```with times as (
SELECT    t1.EmplID
, t3.EmplName
, min(t1.RecTime) AS InTime
, max(t2.RecTime) AS [TimeOut]
, cast(min(t1.RecTime) as datetime) AS InTimeSub
, cast(max(t2.RecTime) as datetime) AS TimeOutSub
, t1.RecDate AS [DateVisited]
FROM  AtdRecord t1
INNER JOIN
AtdRecord t2
ON    t1.EmplID = t2.EmplID
AND   t1.RecDate = t2.RecDate
AND   t1.RecTime < t2.RecTime
inner join
HrEmployee t3
ON    t3.EmplID = t1.EmplID
group by
t1.EmplID
, t3.EmplName
, t1.RecDate
),
CTE_FINAL AS(
SELECT EmplID
,EmplName
,InTime
,[TimeOut]
,[DateVisited]
,datediff(minute,InTimeSub,TimeOutSub) Diffminutes
FROM times )

select *,
case when 60*8>Diffminutes then
'-' + convert(varchar(10),abs(Diffminutes-60*8) /60 )+':'+convert(varchar(10),abs(Diffminutes-60*8) %60)
when 60*8<=Diffminutes then convert(varchar(10),abs(Diffminutes-60*8) /60 )+':'+convert(varchar(10),abs(Diffminutes-60*8) %60)
end from cte```

Satheesh
My Blog

Thursday, January 23, 2014 12:16 PM