7

I want to add a number of months to a date to get a new date. I have a date-field "date", an integer-field "months" and a date-field "new_date". I want to calculate the "new_date" by adding the "months" to the date. I did it with the expression: "date"+ to_interval( concat ( to_string("months"), ' months')) For example: "date"= 2019-06-04, "months" = 12, the result ("newd_date") is: 2020-05-29, but I want to have: 2020-06-04. It seems that QGIS is calculating each month with 30 days. Is there a way to add the exact months?

5

What a shame the to_interval expression treats each month as 30 days!

Here's an ugly expression which will do what you want:

   to_date(concat(year("date")+floor((month("date")+"months"-1)/12), '-',
     right('0' || ((month("date")+"months"-1)%12+1) , 2),'-', 
     right('0' || day("date"),2)))

It breaks the date down into year, month and day, then uses the "months" field to add the relevant amount of years and months, then concatenates the values to make a string to convert to a date. The to_date expression is very particular about the input format - the month and day must be two digits - so the right expressions are used to pad out single digit months/days with a leading zero.

Edit: Now working for when the new month is 12. But this is still a naive solution which will fail when the start date is the 31st of the month and the new month has only 30 days or less.

  • Thank you very much! It works for 12, 24, 36 months! But not for 6 month. Then the new_date ist Null. What can I do? – Nane Oct 7 at 3:39
  • My bad - see updated answer, with corrected sub expression for the year part. – M Bain Oct 7 at 4:37
  • I was one week without internet, so I only tested now the updated answer (thank you for the update!). It is almost what I want. But what will I do with e 31st of the month? – Nane Oct 13 at 18:02
  • It will try to create an impossible date, eg 30th February, 31st September and fail - probably producing a Null. – M Bain Oct 13 at 21:21
  • maybe I can find a solution with case when for the dates wich produces a Null. I will try it... – Nane Oct 14 at 4:21
1

With the help of M Bain I found a solution, because the number of monthes are only 6, 12, 24 and 35. So I did i like this and choosed for the special dates the last existing date: case when day( "date")='31' and month( "date" ) ='03' and "months"=6 then to_date(concat(year("date"), '-09-30' ) ) when day( "date")='31' and month( "date" ) ='05' and "months"=6 then to_date(concat(year("date"), '-11-30' ) ) when day( "date")='29' and month( "date" ) ='08' and "months"=6 then to_date(concat(year("date")+1, '-02-28' ) ) when day( "date")='30' and month( "date" ) ='08' and "months"=6 then to_date(concat(year("date")+1, '-02-28' ) ) when day( "date")='31' and month( "date" ) ='08' and "months"=6 then to_date(concat(year("date")+1, '-02-28' ) ) when day( "date")='31' and month( "date" ) ='10' and "months"=6 then to_date(concat(year("date")+1, '-04-30' ) ) when day( "date")='31' and month( "date" ) ='12' and "months"=6 then to_date(concat(year("date")+1, '-06-30' ) ) else to_date(concat(year("date")+floor((month("date")+"months"-1)/12), '-', right('0' || ((month("date")+"months"-1)%12+1) , 2),'-', right('0' || day("date"),2))) end Thank you for helping me!

  • Well done for sticking with it and covering all the cases! – M Bain Oct 15 at 9:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.