Saturday, February 01, 2014

DATEDIFF IN SQL SERVER

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

4 comments:

  1. Randheer,

    Your posts are really good. Simple, concise yet elaborate.
    Keep up the good work.
    Looking forward to more such posts :)

    Nasar Faraaz

    ReplyDelete
  2. Thanks for the above post.this is really good and provides the informative tips and command about SQL. That is very good sql interview question.

    ReplyDelete