locked
How to Subtract Time from Time in SQL RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    User1912965948 posted

    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.

    Thanks in advance  

    Thursday, January 10, 2019 8:10 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    User-821857111 posted

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

    Thursday, January 10, 2019 8:33 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User1912965948 posted

    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 <g class="gr_ gr_103 gr-alert gr_spell gr_inline_cards gr_disable_anim_appear ContextualSpelling" id="103" data-gr-id="103">datediff</g> function resulted in an overflow. The number of <g class="gr_ gr_104 gr-alert gr_spell gr_inline_cards gr_disable_anim_appear ContextualSpelling" id="104" data-gr-id="104">dateparts</g> separating two <g class="gr_ gr_107 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Grammar multiReplace" id="107" data-gr-id="107">date</g>/time instances is too large. Try to use <g class="gr_ gr_105 gr-alert gr_spell gr_inline_cards gr_disable_anim_appear ContextualSpelling" id="105" data-gr-id="105">datediff</g> with a less precise <g class="gr_ gr_106 gr-alert gr_spell gr_inline_cards gr_disable_anim_appear ContextualSpelling" id="106" data-gr-id="106">datepart</g>.

    Regards

    Thursday, January 10, 2019 8:53 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User-821857111 posted

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

    Thursday, January 10, 2019 9:07 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User1912965948 posted

    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. 

    Thursday, January 10, 2019 9:20 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User-821857111 posted

    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')

    Thursday, January 10, 2019 9:33 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User1912965948 posted

    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 <g class="gr_ gr_53 gr-alert gr_spell gr_inline_cards gr_disable_anim_appear ContextualSpelling" id="53" data-gr-id="53">datediff</g> function resulted in an overflow. The number of <g class="gr_ gr_54 gr-alert gr_spell gr_inline_cards gr_disable_anim_appear ContextualSpelling" id="54" data-gr-id="54">dateparts</g> separating two <g class="gr_ gr_57 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Grammar multiReplace" id="57" data-gr-id="57">date</g>/time instances is too large. Try to use <g class="gr_ gr_55 gr-alert gr_spell gr_inline_cards gr_disable_anim_appear ContextualSpelling" id="55" data-gr-id="55">datediff</g> with a less precise <g class="gr_ gr_56 gr-alert gr_spell gr_inline_cards gr_disable_anim_appear ContextualSpelling" id="56" data-gr-id="56">datepart</g>.

    I imported this data from excel sheet into <g class="gr_ gr_82 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="82" data-gr-id="82">sql</g>.

    Thursday, January 10, 2019 9:46 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User-821857111 posted

    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.

    Thursday, January 10, 2019 10:30 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User77042963 posted

    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

    Thursday, January 10, 2019 3:02 PM