Click here to Skip to main content
15,117,830 members
Please Sign up or sign in to vote.
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
Updated 8-Mar-21 4:48am
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

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

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900