Datediff calculation

Last Post 30 Nov 2005 12:05 PM by nosepicker. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
rpitiyar
New Member
New Member

--
24 Nov 2005 03:29 PM
Hi,

Can anyone explain why select datediff(hh, '1/10/2005 12:53', '1/10/2005 13:02') returns 1. It is apparent that sql takes only hour component in this calcualtion. Shouldn't this return 0 .
if you run select datediff(hh, '1/10/2005 12:53', '1/10/2005 12:02') this will return 0.

Regards

mwesch
New Member
New Member

--
24 Nov 2005 05:22 PM
The DATEDIFF function works by counting "boundaries" crossed. The transition from 12:xx:xx to 13:xx:xx is what increments the value. You can see this for:

select datediff(hh, '1/10/2005 12:59:59', '1/10/2005 13:00:01')
rpitiyar
New Member
New Member

--
24 Nov 2005 08:50 PM
This doesn't give true gap between time values unless you use lowest units (ms or sec) which has limits in the calculation
nosepicker
New Member
New Member

--
30 Nov 2005 12:05 PM
It's true that there are limitations with using DATEDIFF. But there are workarounds. If you want to know the true time difference, try something like this:

SELECT CONVERT(varchar, DATEADD(s, datediff(s, '1/10/2005 12:53', '1/10/2005 13:02'), 0), 108)
SQLUSA
New Member
New Member

--
31 Dec 2005 02:56 AM
I would not do datediff on literal dates.

I would convert them first to datetime format.

You may get surprises with literal dates.


Kalman Toth, SQL Guru
http://www.sqlusa.com/freetrial2005/
SQL Server 2005 Training
khtan
New Member
New Member

--
04 Mar 2006 10:01 PM
basically the rule is if you need to find out the time difference in whatever unit, calculate it in one level lower.
Example, if you need the time difference in hour, use datediff(minute, date1, date2) / 60.0
if you need the difference in minute, then use datediff(second, date1, date2) / 60.0
You are not authorized to post a reply.

Acceptable Use Policy