How to calculate days in sql

Hi Amit,

The answer 3 is correct .

1) The start date is 31-Mar so it will count from Apr,may
and end date is 1-Jun so it will count itself including Jun also.

select DATEDIFF(M,'31-Mar-2014','1-Jun-2014')
answer :3


2) If u want the result should be 2 means
select DATEDIFF(Month,'31-Mar-2014','31-May-2014')
answer:2

Hi Amit,

First of all using only Datediff you won't be getting the exact month count as per your convenient.
Reason : Datediff will just return the number of Month boundaries that have been passed.

If you want month count should be returned as per exact days elapsed,try below query.

declare @startdate datetime
declare @enddate datetime
set @startdate='31-March-2014'
set @enddate='01-June-2014'



select case
        when @startdate > @enddate then NULL
        when DATEPART(day,@startdate) > DATEPART(day,@enddate) then   DATEDIFF(month,@startdate,@enddate)-1
            else DATEDIFF(month,@startdate,@enddate) end as Count_of_month

:-)