How do I convert time to float

I need to get the difference between start times of different days.  I am only looking for specific days where the second clock day is before the prevous clock in day.  The best way to explain my issue would be with some examples.

  clock in day 1                            clock in day 2                                time difference
2015-04-14 08:00:42.067      2015-04-15 07:59:59.717                         .01 minute - valid
2015-04-12 10:52:58.463      2015-04-13 07:49:54.173                       3.03 hours - valid
2015-04-13 08:00:23.065    2015-04-14 08:25:02.007                       .25 minutes - not valid because the second time is later than the first time

The sql I am working with is close.  My output is below but I need to save this time in the database as a float so I can then add it to other values in part of calculating an employees payroll for the week.

   clock in day 1                           clock in day 2                     hh:mm
2015-04-15 07:56:17.913      2015-04-16 07:54:43.170      00:01:00.0000000
2015-04-16 07:54:43.170      2015-04-17 07:51:03.477      00:03:00.0000000
2015-04-17 07:53:46.260      2015-04-18 07:52:51.543      00:00:00.0000000   not valid and need to be excluded in where clause

So with the data in hh:mm columns, I would need to add that value to .15 or 40.00 (as examples) from the database which are stored as floats.  My other issue would be the where clause to exclude anything less than 1 minute.

;
WITH CTE AS
      (
SELECT
      PR.EMPLOYEEID,
      PR.BUSINESS_DATE,
      MIN(PR.CLOCK_IN_DATE_TIME) CLOCK_IN,
      SUM(PR.HOURS_WORKED) HRS_WORKED,
      ROW_NUMBER() OVER (PARTITION BY EMPLOYEEID ORDER BY MIN(CLOCK_IN_DATE_TIME)) RN
FROM Payroll.PUERTORICOTIMECARDS PR
WHERE PR.BUSINESS_DATE >= '04/12/2015'
AND PR.BUSINESS_DATE <= '04/18/2015'
GROUP BY      PR.EMPLOYEEID,
                  PR.BUSINESS_DATE)
SELECT
      C1.EMPLOYEEID,
      C1.BUSINESS_DATE,
      cast (convert(varchar(5),(23-DateDiff(s, c1.clock_in, c2.clock_in)/3600))+':'+convert(varchar(5),(59-DateDiff(s, c1.clock_in, c2.clock_in)%3600/60)) as time) as [hh:mm]
FROM CTE C1
LEFT JOIN CTE C2
      ON C1.EMPLOYEEID = C2.EMPLOYEEID
      AND C1.RN + 1 = C2.RN
WHERE
cast (convert(varchar(5),(23-DateDiff(s, c1.clock_in, c2.clock_in)/3600))+':'+convert(varchar(5),(59-DateDiff(s, c1.clock_in, c2.clock_in)%3600/60)) as time) > '00:00'  (where clause does not work)
axnst2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
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)

Open in new window

axnst2Author Commented:
That is a lot cleaner but I get a value of 94 instead of .02.
Brian CroweDatabase AdministratorCommented:
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.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Mike EghtebasDatabase and Application DeveloperCommented:
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)))
axnst2Author Commented:
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
axnst2Author Commented:
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
Mike EghtebasDatabase and Application DeveloperCommented:
I will build a temp table to try all these conditions. Meanwhile if you think of any other situation like these let me know.
axnst2Author Commented:
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
Mike EghtebasDatabase and Application DeveloperCommented:
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

Open 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

Open 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

Open in new window


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

Mike
axnst2Author Commented:
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
Mike EghtebasDatabase and Application DeveloperCommented:
Hi John,

Other than 8, all others are fixed:
select * From #t
SELECT  ID, IN_Time , Out_Time, iif(durationHour>=0, Duration,60-MinuteOut) As DurationMin--, MinuteOut, durationHour
From (Select SomeID, CLOCK_IN_DATE_TIME
	, CLOCK_OUT_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
	, Convert(decimal(10,1),DATEPART(HOUR, CAST(CLOCK_OUT_DATE_TIME AS DATEtIME))) - 
	  Convert(decimal(10,1),DATEPART(HOUR, CAST(CLOCK_IN_DATE_TIME AS DATEtIME))) As durationHour
	, Convert(decimal(10,1),DATEPART(MINUTE, CAST(CLOCK_OUT_DATE_TIME AS DATEtIME))) As MinuteOut
	From #t) As tt (ID, IN_Time, Out_Time, Duration, durationHour, MinuteOut)
Where Duration >0

Open in new window


Here it is:
1	2015-04-15 08:00:35.747	 2015-04-16 07:56:34.237	 4.0
2	2015-04-16 07:56:34.237	 2015-04-17 07:59:44.823	 3.0
4	2015-04-14 08:00:33.150	 2015-04-15 07:56:21.273	 4.0
5	2015-04-15 07:56:21.273	 2015-04-17 07:58:55.783	 2.0
8	2015-04-14 05:16:05.850	 2015-04-15 07:58:53.870	 42.0

Open in new window


05:16:05.850        \___  needs to produce 2.02 earlier you have said it should be 1.46
07:58:53.870        /

Which is correct 2.02  or 1.46? After you establish this, please describe its logic.

Thanks,

Mike
PortletPaulEE Topic AdvisorCommented:
>>... " 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]
axnst2Author Commented:
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
Mike EghtebasDatabase and Application DeveloperCommented:
To explain the logic I have used thus far to get correct answers for all except number 4, I have calculated the following variables in the FROM clause which is executed first*:
duration = minute_out - minute_in  -- this produces a positive or negative number (or zero)
durationHour = hour_out - hour_in  -- this produces a positive or negative number (or zero)
MinuteOut = the value of minute_out
SELECT  ID, IN_Time , Out_Time, iif(durationHour>=0, Duration,60-MinuteOut) As DurationMin--, MinuteOut, durationHour
From (Select SomeID, CLOCK_IN_DATE_TIME
	, CLOCK_OUT_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
	, Convert(decimal(10,1),DATEPART(HOUR, CAST(CLOCK_OUT_DATE_TIME AS DATEtIME))) - 
	  Convert(decimal(10,1),DATEPART(HOUR, CAST(CLOCK_IN_DATE_TIME AS DATEtIME))) As durationHour
	, Convert(decimal(10,1),DATEPART(MINUTE, CAST(CLOCK_OUT_DATE_TIME AS DATEtIME))) As MinuteOut
	From #t) As tt (ID, IN_Time, Out_Time, Duration, durationHour, MinuteOut)
Where Duration >0

Open in new window


So Where clause, because  Duration >0 in not true the following numbers are excluded:
3, 6, 7
If durationHour>=0, show what ever Duration is.
If is not durationHour>=0, show what (60-MinuteOut) produces.

the above logic handles all except number 4.

I need your help to get a similar mechanical logic. I have read you description of the law and logic but have not been able to wrap my mind around it yet. I will try it couple of more times to see if I can get it. Meanwhile try to show me a way to calculate using the digits in the in and out datetime values similar to the rest.

Thanks,

Mike
-------------------------------------------
* The logical order of execution is:
From
Where
Select
Order By
axnst2Author Commented:
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
Mike EghtebasDatabase and Application DeveloperCommented:
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

Open in new window

axnst2Author Commented:
Mike - I was able to solve it with the use of your code.  Here is the final select that gets everything correct.  The sql is the same but I use the following case statement to determine how to calculate the minutes and hours.  Basically the first when clause determines that total minutes under 1 hour.  If the time is more than an hour, then the second when clause includes the hour.

CASE WHEN DATEDIFF(HOUR, C2.CLOCK_IN, DATEADD(DAY, 1,C1.CLOCK_IN)) >= 0 AND DATEDIFF(HOUR, C2.CLOCK_IN, DATEADD(DAY, 1,C1.CLOCK_IN)) <= 1 THEN (CONVERT(decimal(10,0), DATEPART(minute, CAST(C1.CLOCK_IN AS DATEtIME)))/100.00) + (Convert(decimal(10,1),60 - DATEPART(MINUTE, CAST(C2.CLOCK_IN AS DATETIME)))/100.00)
                   WHEN DATEDIFF(HOUR, C2.CLOCK_IN, DATEADD(DAY, 1,C1.CLOCK_IN)) > 1 THEN DATEDIFF(hour, c2.clock_in, DATEADD(DAY, 1,C1.CLOCK_IN)) + ((CONVERT(decimal(10,0), DATEPART(minute, CAST(DATEADD(DAY, 1,C1.CLOCK_IN) AS DATEtIME)))/100.00) + (Convert(decimal(10,1),60 - DATEPART(MINUTE, CAST(C2.CLOCK_IN AS DATEtIME))))/100.00) end AS delta

Thanks for you help Mike.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperCommented:
I am glad you figured it out. I owe you 500 points now. LOL

Mike
Jim HornMicrosoft SQL Server Data DudeCommented:
( 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.
axnst2Author Commented:
With the assistance of other experts and their code, I was able to solve the problem.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.