Date diff tells the
difference between the two dates in terms of the entity provided.
DATEDIFF (datepart
, startdate , enddate )
Datepart could e mm
or dd or yy or mi depends upon the requirement.
Below are the few
points related to date diff.
DATEDIFF does not guarantee
that the full number of the specified time units passed between
2 datetime Values.
-- Get
difference in hours between 8:55 and 11:00
SELECT DATEDIFF(hh, '08:55', '11:00');
-- Returns
3 although only 2 hours and 5 minutes passed between times
-- Get
difference in months between Sep 30, 2011 and Nov 02, 2011
SELECT DATEDIFF(mm, '2011-09-30', '2011-11-02')
-- Returns
2 although only 1 month and 2 days passed between date
To get the number
of full time units passed
between date times, you can calculate the difference
in lower Units and
then divide by the appropriate number:
SELECT DATEDIFF(mi, '08:55', '11:00')/60;
-- Returns 2 hours now
Randheer,
ReplyDeleteYour posts are really good. Simple, concise yet elaborate.
Keep up the good work.
Looking forward to more such posts :)
Nasar Faraaz
Thanks Nasar
DeleteThanks for the above post.this is really good and provides the informative tips and command about SQL. That is very good sql interview question.
ReplyDeleteThanks Yogesh
Delete