Calculating the difference between two dates in terms of months

[former_member214057]

Hi Guys,

I need to create a calculated column for finding difference between two dates in terms of Month.

Could anyone help!!!

[former_member302041]

select MONTHS_BETWEEN('20181001', '20181203') from dummy

[former_member214057]

Hi Andrey,

Thanks. But in few cases it's not working as expected

[Loed]

Hi,

What do you mean by in terms of months?

What if the dates are 1/1/2019 to 2/15/2019, what would be the result? Is it 1 or 2 months?

What if the dates are 1/31/2019 to 2/15/2019, what would be the result? Is it 1 or 2 months?

Then we can suggest how to solve your problem.

Do you base the "month" with 30 days or is it based on the number of months in your dates?

Regards,

Loed

[azizelmir]

Hi Sagarika,

Please try this:

SELECT DATEDIFF(MONTH, '2019-01-01', '2020-01-01') AS "Months Difference";

Thank you,

Aziz

[former_member590188]

Above SQL statement is throwing error while executing.

Error Message
12:07:15 PM (SQL Editor) Could not execute 'SELECT DATEDIFF(MONTH, '2019-01-01', '2020-01-01') AS "Months Difference"'

Error: (dberror) 257 - sql syntax error: line 1 col 54 (at pos 55)

[azizelmir]

let us check this modification:

SELECT DATEDIFF(MONTH, '2019-01-01', '2020-01-01') AS 'Months Difference'

[hari_prasad1]

Sagarika, let us know in which sceneario it is not calculating as expected.

[former_member590188]

Hi Hari,

Even i am facing same issue reported by Sagarika.

Failed Case is
select MONTHS_BETWEEN('20191031', '20191130') from dummy

This gives result as 0 but expected result is 1.

In ABAP we are able to achieve correct result via using Function Module - HR_AUPBS_MONTH_DAY