I need help rounding times - SQL Server Forums

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)

I need help rounding times

Author

Topic

raritan
Starting Member

39 Posts

Posted - 2008-10-08 : 09:37:38

I have a timeclock table with clock in and clock out times in datetime. To report on this, I am doing a select on the data and using datediff to figure the difference between the times and dividing it by 3600 to convert it from seconds to hours, and I'm using cast to convert it to floating point data.

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

What I'm faced with now is, I need to somehow make this round UP to the nearest six minutes, or in other words have it round up the next 1/10th of an hour (6 min, 12 min, 18, 24, 30, 26, 42, 48, 54, or 60 min). For example:

3 minutes rounds up to 6 minutes
7 minutes rounds up to 12 minutes
1 hr and 23 minutes rounds up to 1 hr and 24 minutes
etc...

I cannot figure out any way to do this. Any ideas? If you need more information or more clear explanation please let me know.

Thank you,
Kevin

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-10-08 : 10:09:07

Edited to round up to next higher 6 minutes.


select
	ET_Rounded =
	-- Formula from Start of Time Period Functions
	-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755
	dateadd(ms,((datediff(ms,0,ET)+359996)/360000)*360000,
	dateadd(hh,datediff(hh,0,ET),0)),
	*
from
	(
	select
		ET	= dateout-datein,
		datein,
		dateout
	from
		(-- Test Data
		select
			datein	= convert(datetime,'20081008 08:00:00.000'),
			dateout	= convert(datetime,'20081008 16:00:00.000')
		union all
		select
			datein	= convert(datetime,'20081008 08:00:00.000'),
			dateout	= convert(datetime,'20081008 16:05:59.997')
		union all
		select
			datein	= convert(datetime,'20081008 08:00:00.000'),
			dateout	= convert(datetime,'20081008 16:06:00.000')
		union all
		select
			datein	= convert(datetime,'20081008 08:00:00.000'),
			dateout	= convert(datetime,'20081008 16:06:00.003')
		union all
		select
			datein	= convert(datetime,'20081008 08:00:00.000'),
			dateout	= convert(datetime,'20081008 16:11:59.997')
		union all
		select
			datein	= convert(datetime,'20081008 08:00:00.000'),
			dateout	= convert(datetime,'20081008 16:12:00.000')
		union all
		select
			datein	= convert(datetime,'20081008 08:00:00.000'),
			dateout	= convert(datetime,'20081008 16:12:00.003')
		) aa
	) a


Results:

ET_Rounded               ET                       datein                   dateout   
-----------------------  -----------------------  -----------------------  -----------------------
1900-01-01 16:00:00.000  1900-01-01 08:00:00.000  2008-10-08 08:00:00.000  2008-10-08 16:00:00.000
1900-01-01 16:06:00.000  1900-01-01 08:05:59.997  2008-10-08 08:00:00.000  2008-10-08 16:05:59.997
1900-01-01 16:06:00.000  1900-01-01 08:06:00.000  2008-10-08 08:00:00.000  2008-10-08 16:06:00.000
1900-01-01 16:12:00.000  1900-01-01 08:06:00.003  2008-10-08 08:00:00.000  2008-10-08 16:06:00.003
1900-01-01 16:12:00.000  1900-01-01 08:11:59.997  2008-10-08 08:00:00.000  2008-10-08 16:11:59.997
1900-01-01 16:12:00.000  1900-01-01 08:12:00.000  2008-10-08 08:00:00.000  2008-10-08 16:12:00.000
1900-01-01 16:18:00.000  1900-01-01 08:12:00.003  2008-10-08 08:00:00.000  2008-10-08 16:12:00.003

(7 row(s) affected)

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-08 : 10:14:15

F_START_OF_X_MIN

E 12°55'05.63"
N 56°04'39.26"

raritan
Starting Member

39 Posts

Posted - 2008-10-08 : 10:20:50

Michael thank you very much, this will work except for that it rounds to the nearest 1/10 hr, either up or down whichever is closer. What I need for it to do is always round up to the next 1/10th of an hr. For example:

1 min - rounds up to 6
2 min - rounds up to 6
3 min - rounds up to 6
4 min - rounds up to 6
5 min - rounds up to 6
6 min - stays at 6
7 min - rounds up to 12
8 min - rounds up to 12
9 min - rounds up to 12
10 min - rounds up to 12
11 min - rounds up to 12
12 min - stays at 12
13 min - rounds up to 18
etc...

Hope that makes sense

Thank you for your help,

Kevin

raritan
Starting Member

39 Posts

Posted - 2008-10-08 : 10:31:21

Peso where can I access the most recent version of the script to add then F_START_OF_X_MIN function, and other F_START_ functions, so I can use them?

Thanks
Kevin

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-08 : 10:36:57

[code]DECLARE @Sample TABLE
(
dt DATETIME
)

INSERT @Sample
SELECT DATEADD(MINUTE, t.Number, d.theDT)
FROM (
SELECT TOP 15
30000 + ABS(CHECKSUM(NEWID())) % 15000 AS theDT
FROM master..spt_values
WHERE Type = 'P'
) AS d
CROSS JOIN (
SELECT Number
FROM master..spt_values
WHERE Type = 'P'
AND Number < 60
) AS t

SELECT dt AS OriginalDateTime,
DATEADD(MINUTE, DATEDIFF(MINUTE, '00:01', dt) / 6 * 6, '00:06') AS RoundedDateTime
FROM @Sample
ORDER BY dt

SELECT GETDATE() AS OriginalDateTime,
DATEADD(MINUTE, DATEDIFF(MINUTE, '00:01', GETDATE()) / 6 * 6, '00:06') AS RoundedDateTime[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-08 : 10:50:13

quote:
Originally posted by raritan

Peso where can I access the most recent version of the script to add then F_START_OF_X_MIN function, and other F_START_ functions, so I can use them?

Follow the link in Michaels post.

E 12°55'05.63"
N 56°04'39.26"

raritan
Starting Member

39 Posts

Posted - 2008-10-08 : 11:34:23

ok so this is what I am working with right now:

SELECT CAST(DATEDIFF(second, timein, timeout) as float) / 3600 AS OriginalDateTime,
DATEADD(MINUTE, DATEDIFF(minute, timein, timeout) / 6 * 6, '00:06') AS RoundedDateTime
FROM TimeClock

And this looks like it is correctly rounding everything up as needed, here is a sample of the results that are returned:

OriginalDateTime RoundedDateTime
4.75 1900-01-01 04:48:00.000
3.3333333333333335 1900-01-01 03:24:00.000
4.583333333333333 1900-01-01 04:36:00.000
3.3333333333333335 1900-01-01 03:24:00.000
4.833333333333333 1900-01-01 04:54:00.000
3.0833333333333335 1900-01-01 03:06:00.000

How can I get this to display just HH:MM on the roundeddatetime? Also additionally, get this to display the hour and minute only, as a floating number, just like the originaldatetime column?

I think it's almost there, thanks so much both of you for your help

Kevin

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-08 : 11:40:59

CONVERT(CHAR(5), DATEADD(MINUTE, DATEDIFF(minute, timein, timeout) / 6 * 6, '00:06'), 108)

E 12°55'05.63"
N 56°04'39.26"

raritan
Starting Member

39 Posts

Posted - 2008-10-08 : 12:07:53

Thanks Peso

raritan
Starting Member

39 Posts

Posted - 2008-10-08 : 16:37:07

ok - terribly sorry but now I've been told that it's too much of a bump to round UP to the next 1/10th of an hour for every time entry, instead they just want it to round either up or down to the NEAREST 1/10th of an hour.

Per the earlier posts I'm currently using this to round UP, and to display it in hours and minutes:
CONVERT(CHAR(5), DATEADD(MINUTE, DATEDIFF(minute, timein, timeout) / 6 * 6, '00:06'), 108)

How can I round this up or down to the nearest 1/10th of an hour? Again very sorry for the repeat posts, just following orders...

Thank you
Kevin

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-10-08 : 16:43:53

Do it the way I showed you in my original post, before you asked me to change it.

CODO ERGO SUM

raritan
Starting Member

39 Posts

Posted - 2008-10-08 : 17:12:40

why would this be adding an extra hr to my roundedtime number?:

SELECT
TimeIn,
TimeOut,
CONVERT(CHAR(5), DATEADD(ms, ((DATEDIFF(ms, TimeIn, TimeOut) + 359996) / 360000) * 360000, DATEADD(hh, DATEDIFF(hh, TimeIn, TimeOut), 0)), 108) as roundedtime
FROM timeclock

results:
timein timeout roundedtime
2008-10-08 13:25:00.000 2008-10-08 14:45:00.000 02:24

Thanks,
Kevin

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-10-08 : 18:00:27

quote:
Originally posted by raritan

why would this be adding an extra hr to my roundedtime number?:

SELECT
TimeIn,
TimeOut,
CONVERT(CHAR(5), DATEADD(ms, ((DATEDIFF(ms, TimeIn, TimeOut) + 359996) / 360000) * 360000, DATEADD(hh, DATEDIFF(hh, TimeIn, TimeOut), 0)), 108) as roundedtime
FROM timeclock

results:
timein timeout roundedtime
2008-10-08 13:25:00.000 2008-10-08 14:45:00.000 02:24

Thanks,
Kevin

Don't know why you are getting an extra hour.

Where did you get the algorithm you are trying?

CODO ERGO SUM

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-08 : 18:18:21

[code]SELECT
TimeIn,
TimeOut,
CONVERT(CHAR(5), DATEADD(ms, ((DATEDIFF(ms, TimeIn, TimeOut) + 359996) / 360000) * 360000, 0), 108) as roundedtime
FROM timeclock[/code]

raritan
Starting Member

39 Posts

Posted - 2008-10-09 : 09:27:48

Michael I scavenged it off of this and your other post that you have the link for up above, after looking at your examples I just started messing around to see what result I could return. Based on hanbingl's post, I do see why it was adding that hour, so I have removed the second dateadd function that I had nested in my algorithm, and replaced it with zero so it doesn't add anything to the rounded date that I am calculating. Now it is not adding an additional hour.

However this does not correctly round to the closest 6th minute increment. If I use this:

CONVERT(CHAR(5), DATEADD(ms, ((DATEDIFF(ms, TimeIn, TimeOut) + 359996) / 360000) * 360000, 0), 108) as roundedtime

I get these results:

timein | timeout | roundedtime
2008-10-09 07:45:00.000 | 2008-10-09 08:50:00.000 | 01:06 | ok
2008-10-09 07:45:00.000 | 2008-10-09 08:55:00.000 | 01:12 | ok
2008-10-09 07:45:00.000 | 2008-10-09 09:00:00.000 | 01:18 | ok
2008-10-09 07:45:00.000 | 2008-10-09 09:05:00.000 | 01:24 | 1:18 is closer, not 1:24
2008-10-09 07:45:00.000 | 2008-10-09 09:10:00.000 | 01:30 | 1:24 is closer, not 1:30
2008-10-09 07:45:00.000 | 2008-10-09 09:15:00.000 | 01:30 | ok
2008-10-09 07:45:00.000 | 2008-10-09 09:20:00.000 | 01:36 | ok
2008-10-09 07:45:00.000 | 2008-10-09 09:25:00.000 | 01:42 | ok
2008-10-09 07:45:00.000 | 2008-10-09 09:30:00.000 | 01:48 | ok
2008-10-09 07:45:00.000 | 2008-10-09 09:35:00.000 | 01:54 | 1:48 is closer, not 1:54
2008-10-09 07:45:00.000 | 2008-10-09 09:40:00.000 | 02:00 | 1:54 is closer, not 2:00
2008-10-09 07:45:00.000 | 2008-10-09 09:45:00.000 | 02:00 | ok

So you see it is rounding UP in each case, instead of rounding to whichever 6th minute increment is closer, even if that means rounding down instead of rounding up - how can I change this?

Thank you,
Kevin

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-09 : 09:45:22

[code]DECLARE @Sample TABLE
(
tIn DATETIME,
tOut DATETIME
)

INSERT @Sample
SELECT '2008-10-09 07:45', '2008-10-09 08:50' UNION ALL
SELECT '2008-10-09 07:45', '2008-10-09 08:55' UNION ALL
SELECT '2008-10-09 07:45', '2008-10-09 09:00' UNION ALL
SELECT '2008-10-09 07:45', '2008-10-09 09:05' UNION ALL
SELECT '2008-10-09 07:45', '2008-10-09 09:10' UNION ALL
SELECT '2008-10-09 07:45', '2008-10-09 09:15' UNION ALL
SELECT '2008-10-09 07:45', '2008-10-09 09:20' UNION ALL
SELECT '2008-10-09 07:45', '2008-10-09 09:25' UNION ALL
SELECT '2008-10-09 07:45', '2008-10-09 09:30' UNION ALL
SELECT '2008-10-09 07:45', '2008-10-09 09:35' UNION ALL
SELECT '2008-10-09 07:45', '2008-10-09 09:40' UNION ALL
SELECT '2008-10-09 07:45', '2008-10-09 09:45'

SELECT tIn,
tOut,
CONVERT(CHAR(8), DATEADD(SECOND, DATEDIFF(SECOND, tIn, tOut) - 1, 0), 108) AS tDiff,
CONVERT(CHAR(8), DATEADD(MINUTE, (DATEDIFF(MINUTE, tIn, tOut) - 1) / 6 * 6, '00:06'), 108) AS tUsed
FROM @Sample[/code]Results are[code]tIn tOut tDiff tUsed
07:45 08:50 01:04:59 01:06:00
07:45 08:55 01:09:59 01:12:00
07:45 09:00 01:14:59 01:18:00
07:45 09:05 01:19:59 01:24:00
07:45 09:10 01:24:59 01:30:00
07:45 09:15 01:29:59 01:30:00
07:45 09:20 01:34:59 01:36:00
07:45 09:25 01:39:59 01:42:00
07:45 09:30 01:44:59 01:48:00
07:45 09:35 01:49:59 01:54:00
07:45 09:40 01:54:59 02:00:00
07:45 09:45 01:59:59 02:00:00[/code]

E 12°55'05.63"
N 56°04'39.26"

raritan
Starting Member

39 Posts

Posted - 2008-10-09 : 13:25:04

Peso thanks for the reply, but unfortunately that returns the same incorrect results that I'm trying to avoid:

- The forth record in the results is rounding up from an hr and 20 min to an hr and 24 minutes, 18 minutes is closer to 20, so I need it to round to 18, not 24.
- The fifth record in the results is rounding up from an hr and 25 min to an hr and 30 minutes, 24 minutes is closer to 25, so I need it to round to 24, not 30.
- The tenth record in the results is rounding up from an hr and 50 min to an hr and 54 minutes, 48 minutes is closer to 50, so I need it to round to 48, not 54.
- The eleventh record in the results is rounding up from an hr and 55 min to the next hr, 54 minutes is closer to 55, so I need it to round to 54, not 60 minutes.

I hope that makes sense? If not please let me know. If you have any other ideas I'd appreciate it.

Thank you,
Kevin

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-09 : 15:21:23

[code]SELECT tIn,
tOut,
CONVERT(CHAR(8), DATEADD(SECOND, DATEDIFF(SECOND, tIn, tOut) - 1, 0), 108) AS tDiff,
CONVERT(CHAR(8), DATEADD(SECOND, (DATEDIFF(SECOND, tIn, tOut) - 181) / 360 * 360, '00:06'), 108) AS tUsed1,
CONVERT(CHAR(8), DATEADD(SECOND, (DATEDIFF(SECOND, tIn, tOut) + 179) / 360 * 360, '00:00'), 108) AS tUsed2
FROM @Sample[/code]Results are[code]tIn tOut tDiff tUsed
07:45 08:50 01:04:59 01:06:00
07:45 08:55 01:09:59 01:12:00
07:45 09:00 01:14:59 01:12:00
07:45 09:05 01:19:59 01:18:00
07:45 09:10 01:24:59 01:24:00
07:45 09:15 01:29:59 01:30:00
07:45 09:20 01:34:59 01:36:00
07:45 09:25 01:39:59 01:42:00
07:45 09:30 01:44:59 01:42:00
07:45 09:35 01:49:59 01:48:00
07:45 09:40 01:54:59 01:54:00
07:45 09:45 01:59:59 02:00:00[/code]

E 12°55'05.63"
N 56°04'39.26"

raritan
Starting Member

39 Posts

Posted - 2008-10-09 : 16:06:39

Peso in amongst the other posts in this thread about the 11th post down, I had said that my reporting need changed and that I now needed to round to the nearest 6th minute. This new method you provided appears to do just that, so thank you very much!

Kevin

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-09 : 16:15:24

cuz he's the man!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources