Post reply

How to find exact no. of months

  • August 12, 2013 at 4:18 pm

    #278590

    How can i find the exact no. of months between two dates?

    select DATEDIFF(dd, '2013-08-05 00:00:00.000','2014-06-01 00:00:00.000'), CEILING(DATEDIFF(dd, '2013-08-05 00:00:00.000','2014-06-01 00:00:00.000')/30.0)

    select DATEDIFF(dd, '2013-08-01 00:00:00.000','2014-07-30 00:00:00.000'), CEILING(DATEDIFF(dd, '2013-08-01 00:00:00.000','2014-07-30 00:00:00.000')/30.0)

    select DATEDIFF(dd, '2013-08-01 00:00:00.000','2014-08-01 00:00:00.000'), CEILING(DATEDIFF(dd, '2013-08-01 00:00:00.000','2014-08-01 00:00:00.000')/30.0)

    I came up with above but 363 & 365 are resulting in 13 months which is not what i want.

  • August 12, 2013 at 4:35 pm

    #1640729

    Will this give you the expected output?

    SELECT DATEDIFF( mm, date1, date2) - CASE WHEN DAY( date1) <= DAY( date2) THEN 0 ELSE 1 END

    FROM (VALUES( '2013-08-05 00:00:00.000','2014-06-01 00:00:00.000'),

    ( '2013-08-01 00:00:00.000','2014-07-30 00:00:00.000'),

    ( '2013-08-01 00:00:00.000','2014-08-01 00:00:00.000')) x(date1, date2)

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • August 13, 2013 at 11:53 am

    #1641060

    Thanks Luis i did some changes and here is what is giving me my expected results.

    SELECT DATEDIFF(DD, date1, date2), DATEDIFF( mm, date1, date2), DATEDIFF( mm, date1, date2) + CASE WHEN DATEDIFF( mm, date1, date2) = 0 OR (MONTH(date1) != MONTH(date2)) THEN 1 ELSE 0 END

    FROM (VALUES

    ( '2013-08-05 00:00:00.000','2013-08-05 00:00:00.000'),

    ( '2013-08-05 00:00:00.000','2014-07-04 00:00:00.000'),

    ( '2013-08-05 00:00:00.000','2014-06-10 00:00:00.000'),

    ( '2013-08-01 00:00:00.000','2014-07-30 00:00:00.000'),

    ( '2013-08-01 00:00:00.000','2014-08-01 00:00:00.000')) x(date1, date2)

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply