How to Subtract Time from Time in SQL RSS

8 replies

Last post Jan 10, 2019 03:02 PM by limno

• fungus.00

Member

70 Points

215 Posts

How to Subtract Time from Time in SQL

Jan 10, 2019 08:10 AM|fungus.00|LINK

Hi all,

I want to get a total working hour per day,

SQL Table

 Emp_Name Emp_No Date ClockIn ClockOut Tom Hardy 1 2018-10-01 2018-10-01 08:41:20 2018-10-01 18:56:19 Tom Hardy 1 2018-10-02 2018-10-02 08:38:40 2018-10-02 21:34:23 Tom Hardy 1 2018-10-03 2018-10-03 08:44:43 2018-10-03 22:42:42 Tom Hardy 1 2018-10-04 2018-10-04 08:53:33 2018-10-04 22:13:35 Tom Hardy 1 2018-10-05 2018-10-05 09:00:03 2018-10-05 23:22:00

I want this output

 Emp_Name Emp_No Date WorkingTime Tom Hardy 1 2018-10-01 10:14 Tom Hardy 1 2018-10-02 12:55 Tom Hardy 1 2018-10-03 13:57 Tom Hardy 1 2018-10-04 13:20 Tom Hardy 1 2018-10-05 14:21

I'll be thankful if someone solves this query.

• Mikesdotnett...

All-Star

194032 Points

28035 Posts

Moderator

Re: How to Subtract Time from Time in SQL

Jan 10, 2019 08:33 AM|Mikesdotnetting|LINK

`SELECT FORMAT(DATEADD(ss,DATEDIFF(ss,[ClockIn], [ClockOut] ),0),'hh:mm')`

• fungus.00

Member

70 Points

215 Posts

Re: How to Subtract Time from Time in SQL

Jan 10, 2019 08:53 AM|fungus.00|LINK

Mikesdotnetting

`SELECT FORMAT(DATEADD(ss,DATEDIFF(ss,[ClockIn], [ClockOut] ),0),'hh:mm')`

Thank you for replying.

`SELECT FORMAT(DATEADD(SS, DATEDIFF(SS, [ClockIn], [ClockOut]), 0),'hh:mm') FROM Attendance`

It gives an error

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

Regards

• Mikesdotnett...

All-Star

194032 Points

28035 Posts

Moderator

Re: How to Subtract Time from Time in SQL

Jan 10, 2019 09:07 AM|Mikesdotnetting|LINK

What values are you operating on to generate that error? And what version of SQL Server are you using?

• fungus.00

Member

70 Points

215 Posts

Re: How to Subtract Time from Time in SQL

Jan 10, 2019 09:20 AM|fungus.00|LINK

Mikesdotnetting

What values are you operating on to generate that error? And what version of SQL Server are you using?

Values mentioned in the main post and I've SQL Server 2014 Express Advanced version.

One thing I want to tell you that ClockIn and ClockOut columns datatype is varchar.

• Mikesdotnett...

All-Star

194032 Points

28035 Posts

Moderator

Re: How to Subtract Time from Time in SQL

Jan 10, 2019 09:33 AM|Mikesdotnetting|LINK

fungus.00

One thing I want to tell you that ClockIn and ClockOut columns datatype is varchar.
Oh.

Try:

`SELECT FORMAT(DATEADD(ss,DATEDIFF(ss, Convert(DateTime, [ClockIn]), Convert(DateTime, [ClockOut])),0),'hh:mm')`

• fungus.00

Member

70 Points

215 Posts

Re: How to Subtract Time from Time in SQL

Jan 10, 2019 09:46 AM|fungus.00|LINK

fungus.00

One thing I want to tell you that ClockIn and ClockOut columns datatype is varchar.

Oh.

Try:

`SELECT FORMAT(DATEADD(ss,DATEDIFF(ss, Convert(DateTime, [ClockIn]), Convert(DateTime, [ClockOut])),0),'hh:mm')`

I tried it

`SELECT FORMAT(DATEADD(ss,DATEDIFF(ss, Convert(DateTime, [ClockIn]), Convert(DateTime, [ClockOut])),0),'hh:mm') FROM Attendance`

return the same error.

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

I imported this data from excel sheet into sql.

• Mikesdotnett...

All-Star

194032 Points

28035 Posts

Moderator

Re: How to Subtract Time from Time in SQL

Jan 10, 2019 10:30 AM|Mikesdotnetting|LINK

At least some of your date and time values are not what you think they are. For the avoidance of potential conversion issues, you should store them as datetimes, not strings.

• limno

All-Star

123252 Points

10024 Posts

Moderator

Re: How to Subtract Time from Time in SQL

Jan 10, 2019 03:02 PM|limno|LINK

Use try_covert function, you may not get anything but your query will run.

```CREATE TABLE mytable(
Emp_Name VARCHAR(9) NOT NULL
,Emp_No   BIT  NOT NULL
,Date     DATE  NOT NULL
,ClockIn  VARCHAR(14) NOT NULL
,ClockOut VARCHAR(15) NOT NULL
);
INSERT INTO mytable(Emp_Name,Emp_No,Date,ClockIn,ClockOut) VALUES
('Tom Hardy',1,'10/1/2018','10/1/2018 8:41','10/1/2018 18:56')
,('Tom Hardy',1,'10/2/2018','10/2/2018 8:38','10/2/2018 21:34')
,('Tom Hardy',1,'10/3/2018','10/3/2018 8:44','10/3/2018 22:42')
,('Tom Hardy',1,'10/4/2018','10/4/2018 8:53','10/4/2018 22:13')
,('Tom Hardy',1,'10/5/2018','10/5/2018 9:00','10/5/2018 23:22');

select Emp_Name,Emp_No,Date
,FORMAT(DATEADD(ss,DATEDIFF(ss,try_convert(datetime,[ClockIn]) , try_convert(datetime,[ClockOut] )),0),'hh:mm') WorkingTime
from mytable

drop table mytable```

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm