Date & time query question (SQL Server 2008)
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|开发者_如何学Python01/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
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
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
精彩评论