How do I convert time to float

I'm not sure if this will help you but at least it's a simpler way to find the difference in the two datetimes

DECLARE @Time1		DATETIME,
	@Time2			DATETIME;

SELECT @Time1 = '2015-04-15 07:56:17.913',
	@Time2 = '2015-04-16 07:54:43.170'

SELECT DATEDIFF(SECOND, CAST(@Time1 AS DATE), @Time1) - DATEDIFF(SECOND, CAST(@Time2 AS DATE), @Time2)

Select allOpen in new window

That is a lot cleaner but I get a value of 94 instead of .02.

That's because it's in seconds seconds.  There are 1 minute and 34 seconds difference between the two times.  If you want it in hours then just divide by 3600.  Performing the original DATEDIFF in seconds just provides more precision.

Using the solution fro Brian, try:

SELECT Convert(decimal(10,1),DATEDIFF(SECOND, CAST(@Time1 AS DATEtIME), CAST(@Time2 AS DATEtIME))/60)

This returns 1438.0 minute. If you want ignore days, try

SELECT  Convert(decimal(10,1),DATEPART(MINUTE, CAST(@Time1 AS DATEtIME))) - Convert(decimal(10,1),DATEPART(MINUTE, CAST(@Time2 AS DATEtIME)))

I tried that and if I do the following, I get:

SELECT (DATEDIFF(SECOND, CAST(@Time1 AS DATE), @Time1) - DATEDIFF(SECOND, CAST(@Time2 AS DATE), @Time2)/3600.00)

returns 28569.0880556

Eghtebas - your suggestion is close, but I am seeing issue if the hour is different.  Here are a few exmplaes and I am not sure how to get the correct time and what would be in the where clause to eliminate the invalid results.

2015-04-15 08:00:35.747      2015-04-16 07:56:34.237      -56.0  --this is valid of 4 minutes
2015-04-16 07:56:34.237      2015-04-17 07:59:44.823      -3.0 --not valid because the second time is later than the first
2015-04-17 07:59:44.823      2015-04-18 08:00:16.780      59.0 --not valid because the second time is later than the first
2015-04-14 08:00:33.150      2015-04-15 07:56:21.273      -56.0 -- should be 4 minutes
2015-04-15 07:56:21.273      2015-04-17 07:58:55.783      -2.0 -- valid and correct

I will build a temp table to try all these conditions. Meanwhile if you think of any other situation like these let me know.

Thanks for your help.  Here are a couple more examples where the difference is greater than an hour.

2015-04-17 06:52:07.033      2015-04-18 08:01:13.373      51.0 - should be 1.09
2015-04-17 06:56:36.507      2015-04-18 08:11:20.213      45.0 - should be 1.15
2015-04-14 05:16:05.850      2015-04-15 07:58:53.870      -42.0 - should be 1.46

Temp Table:

CREATE TABLE #t
( 
    SomeID int
    , CLOCK_IN_DATE_TIME    datetime         
    ,CLOCK_OUT_DATE_TIME   datetime
);

Insert #t(SomeID , CLOCK_IN_DATE_TIME, CLOCK_OUT_DATE_TIME) values
(1 ,   '2015-04-15 08:00:35.747',      '2015-04-16 07:56:34.237')
,(2 ,   '2015-04-16 07:56:34.237',      '2015-04-17 07:59:44.823')
,(3 ,   '2015-04-17 07:59:44.823',      '2015-04-18 08:00:16.780')
,(4 ,   '2015-04-14 08:00:33.150',      '2015-04-15 07:56:21.273')
,(5 ,   '2015-04-15 07:56:21.273',      '2015-04-17 07:58:55.783')
,(6 ,   '2015-04-17 06:52:07.033',      '2015-04-18 08:01:13.373')
,(7 ,   '2015-04-17 06:56:36.507',      '2015-04-18 08:11:20.213')
,(8 ,   '2015-04-14 05:16:05.850',      '2015-04-15 07:58:53.870')

select * From #t

SomeID , CLOCK_IN_DATE_TIME      CLOCK_OUT_DATE_TIME
1	2015-04-15 08:00:35.747	 2015-04-16 07:56:34.237
2	2015-04-16 07:56:34.237	 2015-04-17 07:59:44.823
3	2015-04-17 07:59:44.823	 2015-04-18 08:00:16.780
4	2015-04-14 08:00:33.150	 2015-04-15 07:56:21.273
5	2015-04-15 07:56:21.273	 2015-04-17 07:58:55.783
6	2015-04-17 06:52:07.033	 2015-04-18 08:01:13.373
7	2015-04-17 06:56:36.507	 2015-04-18 08:11:20.213
8	2015-04-14 05:16:05.850	 2015-04-15 07:58:53.870

Select allOpen in new window

Existing code and results:

SELECT  ID, IN_Time , Out_Time, Duration
From (Select SomeID, CLOCK_IN_DATE_TIME
	, CLOCK_IN_DATE_TIME
	, Convert(decimal(10,1),DATEPART(MINUTE, CAST(CLOCK_OUT_DATE_TIME AS DATEtIME))) - 
	  Convert(decimal(10,1),DATEPART(MINUTE, CAST(CLOCK_IN_DATE_TIME AS DATEtIME))) As duration
	From #t) As tt (ID, IN_Time, Out_Time, Duration)
Where Duration >0

Select allOpen in new window

Update your comments per each row by row id number:

ID       IN_Time                   Out_Time                    Duration
1	2015-04-15 08:00:35.747	   2015-04-15 08:00:35.747	   56.0
2	2015-04-16 07:56:34.237	   2015-04-16 07:56:34.237	   3.0
4	2015-04-14 08:00:33.150	   2015-04-14 08:00:33.150	   56.0
5	2015-04-15 07:56:21.273	   2015-04-15 07:56:21.273	   2.0
8	2015-04-14 05:16:05.850	   2015-04-14 05:16:05.850	   42.0

Select allOpen in new window

Based on your notes we can make the necessary adjustment to the code.

Mike

Mike - Here are my comments per row number.

1.  Should be 4 minutes.
2.  Correct
4.  Should be 4 minutes.
4.  Correct
8.  Should be 2.02 hours.

Thanks.

John

>>... " I am only looking for specific days where the second clock day is before the previous clock in day."

then why not just compare them without any conversion?

where [clock in day 2]   <  [clock in day 1]

Sorry Mike...it should be 1.46.  This specific case should be excluded because the employee clocked in later on the second day than the first day.  This would be excluded because the difference between the date time is greater than 24 hrs.  The logic I am working on is for Puerto Rico labor laws and this specific case is the 24 hr rule.

Now if the times were switched for the dates, then it would be valid.  For example:

             Day 1                                    Day 2
2015-04-14 07:58:53.870      2015-04-15 05:16:05.850

this would be valid because the employee clocked in on the second day before the 24 hr period.  For this rule to not be valid on this example, the employee would need to clock in at 7:58 or later on day 2.


The best way to explain the law is by the examples and explanation I was provided.  

Example I
            Day 1                                          Day 2
8:00 am -- 12:00 pm                         7:00 am -- 12:00 pm
2:00 pm -- 1:00 pm (meal)                   12:00 pm -- 1:00 pm (meal)
:00 pm -- 5:00 pm                                 1:00 pm -- 5:00 pm

In Example I, notice that the employee commenced the shift one (1) hour earlier on the second day, in relation to the first day. Therefore, under the “24 hour” rule, the hour worked
between 7:00 am and 8:00 am on the second day is considered overtime. This conclusion is reached by back tracking the amount of hours worked between 8:00 am on the second day to 8:00
am on the first day. The sum of hours worked in this period is nine (9) hours, or one (1) in excess of the statutory eight (8) hour maximum. Thus, to determine if each hour worked on the
second day is in excess of eight (8) in the consecutive twenty four (24) hour period that concludes with said hour, you must count the hours worked in the preceding twenty four (24) hour
period.

Additionally, in this example, notwithstanding that the employee physically worked nine (9) hours on the second day, the employee would only be paid one (1) hour of overtime, pursuant
to the “no-pyramidation” rule discussed earlier. This is because the hour from 7:00 am to 8:00 am on the second day was already calculated as overtime under the “24 hour” rule. Therefore, this
hour is excluded from the calculation of subsequent overtime. As a follow-up, assume the employee worked on the second day from 7:00 am to 6:00 pm. In this case, the employee would be entitled
to two (2) hours of overtime: the hour from 7:00 am to 8:00 am under the “24 hour” rule and the hour from 5:00 pm to 6:00 pm for physical work in excess of eight (8) hours on Day 2.

Example II
            Day 1                                          Day 2
8:00 am -- 12:00 pm                        8:00 am -- 1:00 pm
2:00 pm -- 1:00 pm (meal)                    1:00 pm -- 2:00 pm (meal)
1:00 pm -- 5:00 pm                                2:00 pm -- 5:00 pm

In Example II, notice that the employee commenced the meal period on the second day, one (1) hour later than on the first day. In this scenario, when you backtrack the 24 hour period
from 1:00 pm on Day 2 to 1:00 pm on the first day, you will notice that the employee worked nine (9) hours. Consequently, the hour worked between 12:00 pm to 1:00 pm on the second day is
considered overtime under the “24 hour” rule.


Thanks.

John

Ok...if I understand what you are asking here is the logic broken out.

So here is an employees time card for two days

Day 1 In/out  (employee took 30 minute lunch)

04/15/2015 08:00 am - 04/15/2015 12:15 pm
04/15/2015 12:45 pm - 04/15/2015 6:30 pm  

Day 2  In/Out (Employee took 1 hr lunch)

04/16/2015 07:50 am - 04/16/2015 12:00 pm
04/16/2015 1:00 pm - 04/16/2015 7:00 pm

So in this example I would first look at 04/15/2015 08:00 and add a day to it to 04/16/2015 08:00 am  Now I would take that datetime and subtract it from the second datetime of 04/16/2015 07:50 am and that would give me 10 minutes under the rule.  Because the employee clock in 24 hours before his last clock in the morning before, he would get paid double time.  This same logic would also be applied to the lunch time clock in.  In that case, 04/15/2015 12:45 pm would add a day to get 04/16/2015 12:45 pm and compare to the same day clock in of 04/16/2015 1:00 pm  the employee would NOT get paid double time because he clocked in 15 minutes after his prevoius day clock in which makes the time greater than 24 hrs.

If we look at an example where the employee has a day off in between, that would not be included in the 24 hr rule because the time difference would be greater than 24 hours.

Thanks for you help on this.

John

Q1: Is Duration column overtime?
Q2: Other than item 8 which is not correct yet, are all duration values positive number?

ID       IN_Time                   Out_Time                    Duration
1	2015-04-15 08:00:35.747	   2015-04-15 08:00:35.747	   4.0
2	2015-04-16 07:56:34.237	   2015-04-16 07:56:34.237	   3.0
4	2015-04-14 08:00:33.150	   2015-04-14 08:00:33.150	   4.0
5	2015-04-15 07:56:21.273	   2015-04-15 07:56:21.273	   2.0
8	2015-04-14 05:16:05.850	   2015-04-14 05:16:05.850	   42.0

Select allOpen in new window

I am glad you figured it out. I owe you 500 points now. LOL

Mike

( apologies if this is a duplicate of the above comments )

I ran into this issue with a previous client that was an airline, as they had to calculate flight times where the OUT and IN went across midnight, and the way I handled it was to store the difference as a datetime value like '1900-01-01 23:04:58.470' for 23 hours, 4 minutes, etc.  That way the value can handle times greater than 24 hours.  

The plus side is you still get to use the DATEDIFF() and DATEADD()  functions as well as adding and sum.
The only downside I remember is that when reporting off of it and converting back to hours it had a habit of adding a day, which I intentionally had to handle in an SSRS expression.

Good luck.

With the assistance of other experts and their code, I was able to solve the problem.