How to subtract the intime and outtime in SQL

Question

1.00/5 (1 vote)

See more:

Thanks in advance!!! I need to Minus the Intime and Stepout time and get the result as HH:MM in sql. Now I need to minus the (@InOutDate - @SteppedDate)....Pls help

@InOutDate = 34:00:00
@SteppedDate= 01:00:00

What I have tried:

 Declare @SteppedDate varchar(100)
 Declare @InOutDate varchar(100)

  Set @SteppedDate = (select format(convert(int, datediff(s, SteppedOut, SteppedIn)/3600), 'd2')+':'+format(convert(int, datediff(s, SteppedOut, SteppedIn)%3600/60), 'd2')+':'+format(convert(int, datediff(s, SteppedOut, SteppedIn)%60), 'd2') as [hh:mm:ss]
from Attendance where AttendanceID=4776)

 Set @InOutDate = (
 select format(convert(int, datediff(s, InTime, OutTime)/3600), 'd2')+':'+format(convert(int, datediff(s, InTime, OutTime)%3600/60), 'd2')+':'+format(convert(int, datediff(s, InTime, OutTime)%60), 'd2') as datetime
from Attendance where AttendanceID=4776
)

select @SteppedDate,@InOutDate

Posted 8-Mar-21 0:25am

Member 15028582

Updated 8-Mar-21 3:48am

Add a Solution

Comments

OriginalGriff 8-Mar-21 6:35am

And?
What does it do that you didn't expect, or not do that you did?
What have you tried to do to find out why?
Are there any error messages, and if so, where and when? What did you do to make them happen?

This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with.
Use the "Improve question" widget to edit your question and provide better information.

Member 15028582 8-Mar-21 6:42am

Actually ji....Just Imagine u have 2 columns...First column is INTime and Second column is OutTime....InTime=34:00:00 and OutTime=01:00:00...Both the columns are varchar(100) datatype....Now How can you minus the (InTime - outtime) as Finaltime column(new column) using sql query...Actually I have tried But the intime is greater than 24 hours so that it shows conversion failed error....

Richard MacCutchan 8-Mar-21 6:55am

Why are you using VARCHAR for Date/Time values?

Member 15028582 8-Mar-21 7:01am

Actually Ji....I have 4 columns....InTime,Outtime,stepintime,stepouttime....I need to first take the difference from (intime - outtime) as InoutTime after that I need to take the difference from (stepintime - stepouttime) as StepinTime and finally I need to subtract the (InoutTime - stepouttime) as Newcolumn result....So that first I Used to get the (intime - outtime) data and then (stepintime - stepouttime) data and the final step is pending pls help..

Richard MacCutchan 8-Mar-21 7:09am

Then all these columns should be DateTime types so you can do the calculations easily.

Member 15028582 8-Mar-21 7:10am

Yes all columns are datetime only...give some example ji...Pls

Richard MacCutchan 8-Mar-21 7:12am

What do you mean, examples of what?

CHill60 8-Mar-21 8:51am

You have said "Both the columns are varchar(100) datatype" and you have also said "Yes all columns are datetime only" - which is it?

Member 15028582 8-Mar-21 7:23am

How can we write.....easily ji...

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2021-03-08T03:48:00

Quote:
But the intime is greater than 24 hours so that it shows conversion failed error....

There is no such time as 34 hours. So what does an InDate = '34:00:00' really mean?

If it means "34 hours after a set time" then you can do something like

SQL

declare @basetime time = '00:00:00'
declare @intime time = dateadd(hh,34, @basetime)

So @intime ends up as '10:00:00' - but on what day? It's meaningless data, it imparts no information.

So the advice given by @Richard-MacCutchan is spot on - store this information in datetime columns. E.g.

SQL

declare @inday datetime = '2021-03-08 00:00:01.000'
declare @outday datetime = '2021-03-09 01:10:05.000'

You can then use standard functions to get the time differences as integers. For example

SQL

declare @diffWholeHOURS int = (select DATEDIFF(HOUR, @inday, @outday))
declare @diffRemainingMinutes int = ((select DATEDIFF(MINUTE, @inday, @outday)) - (@diffWholeHOURS) *60)

The key thing to note here is that I'm not using any clunky and unnecessary conversions and I'm using the correct data types throughout.

Your requirement to get the results in HH:MM format is probably leading you astray. It goes back to your InDate = '34:00:00' problem. That semi-colon seems to be making you think that you are going to get a "time" back. You are not. It's a contrived display format so that needs to be the very last thing you do. In fact, it should really be done in your UI layer. But you could do

SQL

select FORMAT(@diffWholeHOURS, '00') + ':' + FORMAT(@diffRemainingMinutes, '00')

How to subtract the intime and outtime in SQL

1 solution

Solution 1

Add your solution here

Preview 0

How to subtract the intime and outtime in SQL

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us