Date & time query question (SQL Server 2008)

Question

hi

I have table that contain date and time field.

id|date|time
=========
1|01/01/2001|10:45
2|01/02/2002|11:45
3|01/03/2003|12:45
4|01/04/2004|12:55

I need to know the difference between the MAX(date) and the MIN(date)

And the MAX(time) and the MIN(time)

Something like.... MAX(date)-MIN(date) ???.....

Thanks in advance

Answer 1

DATEDIFF is your friend.

To get the difference in number of days: SELECT DATEDIFF(day, MIN(date), MAX(date)) FROM [table] = 1186

To get the difference in number of hours: SELECT DATEDIFF(hour, MIN(time), MAX(time)) FROM [table] = 2

To get the difference in number of minutes: SELECT DATEDIFF(minute, MIN(time), MAX(time)) FROM [table] = 130

Answer 2

A very simple way to get what you are asking is ...

SELECT 
[DifferanceInMinutes] = DATEDIFF(mi,(SELECT MIN(YourDate) FROM MyTable),(SELECT MAX(YourDate) FROM MyTable))

http://msdn.microsoft.com/en-us/library/ms189794.aspx