DateDiff and Rounding - 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)

DateDiff and Rounding

Author

Topic

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-01-24 : 13:52:45

I want to be able to take a given date range, calculate the number of minutes elasped, and round up to the next minute (think cell phone).

Here's what I have so far. The desired result is 20, and not 19.72 or 20.00


SELECT (DATEDIFF(ss, '2003-01-07 17:23:39.107', '2003-01-07 17:43:22.580') / 60.00)  -- returns 19.716666

SELECT (ROUND((DATEDIFF(ss, '2003-01-07 17:23:39.107', '2003-01-07 17:43:22.580') / 60.00),2)) -- returns 19.72

SELECT (ROUND(ROUND((DATEDIFF(ss, '2003-01-07 17:23:39.107', '2003-01-07 17:43:22.580') / 60.00),0),0)) -- returns 20.000

Thanks for any help guys!
Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-01-24 : 13:55:29

Wait a sec, I think this works, or is pretty close. I don't think this always rounds up though...

 
SELECT CAST((ROUND(ROUND((DATEDIFF(ss, '2003-01-07 17:23:39.107', '2003-01-07 17:43:22.580') / 60.00),0),0)) as INT) -- returns 20

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-01-24 : 14:19:13

I'm having real bad luck with date problems today ... but have you tried ceiling()?

Jay White
{0}

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-01-24 : 14:43:38

I've never seen that function, but after looking in the BOL, that might be the solution.

This is where I'm at now. I think it works perfectly, except if the datediff is exactly 20 minutes.


SELECT CAST((((DATEDIFF(ss, '2003-01-07 17:23:39.107', '2003-01-07 17:43:40.107') / 60.00) + 1)) AS INT) as RoundedResult, (DATEDIFF(ss, '2003-01-07 17:23:39.107', '2003-01-07 17:43:40.107') / 60.00) as Result, 21 as DesiredResult

SELECT CAST((((DATEDIFF(ss, '2003-01-07 17:23:39.107', '2003-01-07 17:43:39.107') / 60.00) + 1)) AS INT) as RoundedResult, (DATEDIFF(ss, '2003-01-07 17:23:39.107', '2003-01-07 17:43:39.107') / 60.00) as Result, 20 as DesiredResult

SELECT CAST((((DATEDIFF(ss, '2003-01-07 17:23:39.107', '2003-01-07 17:43:38.107') / 60.00) + 1)) AS INT) as RoundedResult, (DATEDIFF(ss, '2003-01-07 17:23:39.107', '2003-01-07 17:43:38.107') / 60.00) as Result, 20 as DesiredResult

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-01-24 : 14:47:40

I think CEILING() is the solution! Thanks Jay

 
SELECT CEILING((DATEDIFF(ss, '2003-01-07 17:23:39.107', '2003-01-07 17:43:40.107') / 60.00)), (DATEDIFF(ss, '2003-01-07 17:23:39.107', '2003-01-07 17:43:40.107') / 60.00) as Result, 21 as DesiredResult

SELECT CEILING((DATEDIFF(ss, '2003-01-07 17:23:39.107', '2003-01-07 17:43:39.107') / 60.00)), (DATEDIFF(ss, '2003-01-07 17:23:39.107', '2003-01-07 17:43:39.107') / 60.00) as Result, 20 as DesiredResult

SELECT CEILING((DATEDIFF(ss, '2003-01-07 17:23:39.107', '2003-01-07 17:43:38.107') / 60.00)), (DATEDIFF(ss, '2003-01-07 17:23:39.107', '2003-01-07 17:43:38.107') / 60.00) as Result, 20 as DesiredResult

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources