help - need to round time to nearest 5 minutes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

help - need to round time to nearest 5 minutes

Author

Topic

raritan
Starting Member

39 Posts

Posted - 2007-08-13 : 09:45:54

I have a table where time clock entries are recorded. There is a TimeIn and a TimeOut column, both of which are datetime datatype.

I need an update query that will take an entry and round it up or down based on the following criteria:

- If it is at 4 minutes (or 9 minutes), it rounds up to the nearest
five or ten minute mark.
- If it is less then 4 it rounds down
- If it is greater than five but less than nine, it rounds down to
five

For example, someone punches in at 2007-08-13 08:00:00.000

If they had punched out at 09:13:45.000, then it would round down to 09:10:00.000

If they had punched out at 09:14:45.000, then it would round up to 09:15:00.000

If they had punched out at 09:18:45.000, then it would round down to 09:15:00.000

If they had punched out at 09:19:45.000, then it would round up to 09:20:00.000

Can anyone help me with this? After I figure out how to get it to update correctly, I'm going to put it into an insert trigger on this table, so that it will update automatically when an entry is made.

Thank you,
Kevin

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-13 : 10:22:31

[code]DECLARE @TABLE TABLE
(
time_out datetime
)

INSERT INTO @TABLE (time_out)
SELECT '09:10:00.000' UNION ALL
SELECT '09:13:45.000' UNION ALL
SELECT '09:14:00.000' UNION ALL
SELECT '09:14:45.000' UNION ALL
SELECT '09:18:45.000' UNION ALL
SELECT '09:19:45.000'

SELECT time_out, CASE WHEN DATEDIFF(second, DATEADD(second, DATEDIFF(second, 0, time_out) / 300 * 300, 0), time_out) >= 240
THEN DATEADD(second, (DATEDIFF(second, 0, time_out) / 300 * 300) + 300, 0)
ELSE DATEADD(second, DATEDIFF(second, 0, time_out) / 300 * 300, 0)
END
FROM @TABLE
/*
time_out
------------------------------------------------------ ------------------------------------------------------
1900-01-01 09:10:00.000 1900-01-01 09:10:00.000
1900-01-01 09:13:45.000 1900-01-01 09:10:00.000
1900-01-01 09:14:00.000 1900-01-01 09:15:00.000
1900-01-01 09:14:45.000 1900-01-01 09:15:00.000
1900-01-01 09:18:45.000 1900-01-01 09:15:00.000
1900-01-01 09:19:45.000 1900-01-01 09:20:00.000
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-13 : 10:33:06

check this out.

http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

Ashley Rhodes

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-13 : 10:37:04

KH

he is also going to have dates in his data.

TRY THIS

create table #TIME_stamp(timein datetime, timeout datetime)

insert into #time_stamp values ('2007-08-13 08:00:00.000' , '2007-08-13 09:13:45.000')
insert into #time_stamp values ('2007-08-13 08:00:00.000' , '2007-08-13 09:14:45.000')
insert into #time_stamp values ('2007-08-13 08:00:00.000' , '2007-08-13 09:18:45.000')
insert into #time_stamp values ('2007-08-13 08:00:00.000' , '2007-08-13 09:19:45.000')

SELECT timeout, CASE WHEN DATEDIFF(second, DATEADD(second, DATEDIFF(second, 0, timeout) / 300 * 300, 0), timeout) >= 240
THEN DATEADD(second, (DATEDIFF(second, 0, timeout) / 300 * 300) + 300, 0)
ELSE DATEADD(second, DATEDIFF(second, 0, timeout) / 300 * 300, 0)
END
from #Time_Stamp

I GET THIS ERROR

Server: Msg 535, Level 16, State 1, Line 1
Difference of two datetime columns caused overflow at runtime.

Ashley Rhodes

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-13 : 10:43:09

use Jeff's TimeOnly() function in the link posted or MVJ's F_TIME_FROM_DATETIME
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762&SearchTerms=F_TIME_FROM_DATETIME

KH
[spoiler]Time is always against us[/spoiler]

raritan
Starting Member

39 Posts

Posted - 2007-08-13 : 11:03:11

Thank you for your reply khtan, but when I try to run this select statement against one of my data records, like this:

SELECT TimeOut,
CASE WHEN DATEDIFF(second, DATEADD(second, DATEDIFF(second, 0, timeout) / 300 * 300, 0), timeout) >= 240
THEN DATEADD(second, (DATEDIFF(second, 0, timeout) / 300 * 300) + 300, 0)
ELSE DATEADD(second, DATEDIFF(second, 0, timeout) / 300 * 300, 0)
END
FROM TimeClock
WHERE CashierID = 21

I get the following error message:

Server: Msg 535, Level 16, State 1, Line 1
Difference of two datetime columns caused overflow at runtime.

Any ideas as to why?

Thanks,
Kevin

raritan
Starting Member

39 Posts

Posted - 2007-08-13 : 11:09:04

never mind, i see why - it's because my timeout column is datetime and not just time - if i change:

timeout

to:

dbo.timepart(timeout)

it works and correctly rounds the minute. but it doesn't handle the date correctly - it changes:

2007-08-13 09:09:45.000

to:

1900-01-01 09:10:00.000

How do I get that to return the date portion as is, and just change the time portion?

Thanks,
Kevin

raritan
Starting Member

39 Posts

Posted - 2007-08-13 : 11:11:20

I also get the overflow error when trying to use Ashley Rhode's query posted above.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-13 : 11:13:35

can you post your table structure, sample data and the expected output ?

KH
[spoiler]Time is always against us[/spoiler]

raritan
Starting Member

39 Posts

Posted - 2007-08-13 : 13:07:43

Here's my table structure:
ID (int, Not Null)
CashierID (int, Not Null)
TimeIn (datetime, Not Null)
TimeOut (datetime, Null)
DBTimeStamp (timestamp, Null)

Here's the data from one of my records:
ID = 6620
CasherID = 21
TimeIn = 2007-08-13 08:00:00.000
TimeOut = 2007-08-13 09:09:45.000
DBTimeStamp = 0x00000000000AC1EA

When I run the query to update for example the TimeOut column, to continue with our example, my result should be the timeout column's value is changed to:

2007-08-13 09:10:00.000

If you need any more info let me know. Thank you,
Kevin

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-08-13 : 13:50:45

[code]

select
a.TimeIn,
TimeInRounded =
dateadd(mi,(datepart(mi,dateadd(mi,1,a.TimeIn))/5)*5,dateadd(hh,datediff(hh,0,dateadd(mi,1,a.TimeIn)),0))
from
(
select TimeIn = convert(datetime, '2007-08-13 13:00:00' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:01:00' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:02:00' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:03:00' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:03:59' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:04:00' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:05:00' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:06:00' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:07:00' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:08:00' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:08:59' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:09:00' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:10:00' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:11:00' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:12:00' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:13:00' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:13:59' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:14:00' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:15:00' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:16:00' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:17:00' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:18:00' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:18:59' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:19:00' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:20:00' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:21:00' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:22:00' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:23:00' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:23:59' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:24:00' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:25:00' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:26:00' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:27:00' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:28:59' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:28:00' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:29:00' ) union all
select TimeIn = convert(datetime, '2007-08-13 13:30:00' )
) a
order by
a.TimeIn

Results:
TimeIn TimeInRounded
------------------------------------------------------ ------------------------
2007-08-13 13:00:00.000 2007-08-13 13:00:00.000
2007-08-13 13:01:00.000 2007-08-13 13:00:00.000
2007-08-13 13:02:00.000 2007-08-13 13:00:00.000
2007-08-13 13:03:00.000 2007-08-13 13:00:00.000
2007-08-13 13:03:59.000 2007-08-13 13:00:00.000
2007-08-13 13:04:00.000 2007-08-13 13:05:00.000
2007-08-13 13:05:00.000 2007-08-13 13:05:00.000
2007-08-13 13:06:00.000 2007-08-13 13:05:00.000
2007-08-13 13:07:00.000 2007-08-13 13:05:00.000
2007-08-13 13:08:00.000 2007-08-13 13:05:00.000
2007-08-13 13:08:59.000 2007-08-13 13:05:00.000
2007-08-13 13:09:00.000 2007-08-13 13:10:00.000
2007-08-13 13:10:00.000 2007-08-13 13:10:00.000
2007-08-13 13:11:00.000 2007-08-13 13:10:00.000
2007-08-13 13:12:00.000 2007-08-13 13:10:00.000
2007-08-13 13:13:00.000 2007-08-13 13:10:00.000
2007-08-13 13:13:59.000 2007-08-13 13:10:00.000
2007-08-13 13:14:00.000 2007-08-13 13:15:00.000
2007-08-13 13:15:00.000 2007-08-13 13:15:00.000
2007-08-13 13:16:00.000 2007-08-13 13:15:00.000
2007-08-13 13:17:00.000 2007-08-13 13:15:00.000
2007-08-13 13:18:00.000 2007-08-13 13:15:00.000
2007-08-13 13:18:59.000 2007-08-13 13:15:00.000
2007-08-13 13:19:00.000 2007-08-13 13:20:00.000
2007-08-13 13:20:00.000 2007-08-13 13:20:00.000
2007-08-13 13:21:00.000 2007-08-13 13:20:00.000
2007-08-13 13:22:00.000 2007-08-13 13:20:00.000
2007-08-13 13:23:00.000 2007-08-13 13:20:00.000
2007-08-13 13:23:59.000 2007-08-13 13:20:00.000
2007-08-13 13:24:00.000 2007-08-13 13:25:00.000
2007-08-13 13:25:00.000 2007-08-13 13:25:00.000
2007-08-13 13:26:00.000 2007-08-13 13:25:00.000
2007-08-13 13:27:00.000 2007-08-13 13:25:00.000
2007-08-13 13:28:00.000 2007-08-13 13:25:00.000
2007-08-13 13:28:59.000 2007-08-13 13:25:00.000
2007-08-13 13:29:00.000 2007-08-13 13:30:00.000
2007-08-13 13:30:00.000 2007-08-13 13:30:00.000

(37 row(s) affected)

[/code]

CODO ERGO SUM

raritan
Starting Member

39 Posts

Posted - 2007-08-13 : 14:33:39

Michael that appears to do exactly what I need - thank you very much. And thanks everyone else for your help as well.

Kevin

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-13 : 14:39:45

Brillian stuff MVJ

i was trying to do something stupid but it did not work

like

select substring(convert(varchar(20), datepart(minute,timeout)),2,1) from #time_stamp

Is there anyway we can get this to work.

Can you explain how your query works in a few words.

Ashley Rhodes

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-08-13 : 15:05:08

quote:
Originally posted by ashley.sql
...Can you explain how your query works in a few words...

First, add one minute to the Time.
Next, find the start of the 5 minute time period using the algorithim from the F_START_OF_05_MIN function on this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755

This algorithim is even simpler, but it stops working in the year 5983.


select
	a.TimeIn,
	TimeInRounded =
		dateadd(mi,(datediff(mi,0, dateadd(mi,1,a.TimeIn))/5)*5,0)

CODO ERGO SUM

raritan
Starting Member

39 Posts

Posted - 2007-08-13 : 15:29:49

Now that this is done, I have another related question. I have a report set up that calculates the hours worked by using this:

cast(datediff(second, timein, timeout) as float) / 3600

this returns the hours and minutes in decimal instead of showing hours and minutes.

For example, say I have the following TimeIn and TimeOut values:

TimeIn = 2007-08-06 14:35:00.000
TimeOut = 2007-08-06 18:05:00.000

The total hours worked will be shown in the report as 3.50.

I would like this to instead show as 3:30, for three hrs and 30 minutes.

Is this possible?

Thanks,
Kevin

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-08-13 : 17:49:16

[code]
select
ElapsedTime = TimeOut-TimeIn,
ElapsedTimeString =
-- If Elapsed Time is always less than 24 hours
left(convert(varchar,TimeOut-TimeIn,108),5),
ElapsedTimeGT24 =
-- If Elapsed Time can be greater than or equal 24 hours
right('0000'+right(datediff(hh,0,TimeOut-TimeIn),4),4)+':'+
datename(mi,TimeOut-TimeIn)
from
(
-- Test data
select
TimeIn = convert(datetime,'2007-08-06 14:35:00.000') ,
TimeOut = convert(datetime,'2007-08-06 18:05:00.000')
) a

Results:
ElapsedTime ElapsedTimeString ElapsedTimeGT24
------------------------------------------------------ ----------------- -----------------------------------
1900-01-01 03:30:00.000 03:30 0003:30

(1 row(s) affected)
[/code]

CODO ERGO SUM

raritan
Starting Member

39 Posts

Posted - 2007-08-13 : 18:04:01

Works great - thanks for all your help Michael

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources