Use the DATEDIFF functions ... for example SELECT DATEDIFF ( datepart , startdate , enddate )This will return the number of days difference.SELECT DATEDIFF(d,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')returns 2 daysThis returns the total number of hours difference (52)SELECT DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')--combining every thing I think will give you what you are looking for:SELECT DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')/24 AS 'Days' ,DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013') - (DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:45:41.013')/24)*24 AS 'Hours'Result:DaysHours24
This will only works, when minutes and seconds are same of both the dates, but if we change minutes then values are not correct.
Like: SELECT DATEDIFF(hh,'2013-04-05 10:45:41.013','2013-04-07 14:00:41.013')
I have changed the minutes in this query,
After executing this query we still get the same output i.e. 4 hours
But According to me output should be 3 hours,
Can you tell me how we can get 3 hours output.
Please help
Thanks
Deepak Sharma
You need to understand that DATEDIFF is counting the number of boundaries you are crossing based on the date type you are using, which in your case is hours (hh).