I have two dates, e.g. '2022-01-03'
and '2022-03-04'
, is there any neat way to calculate ONLY the completed full calendar months between these dates?
Some examples with their requested outputs:
'2022-01-03' and '2022-03-04'
full calendar months = 1 since only February was a full calendar month between this timespan.
'2022-01-01' and '2022-05-30'
full calendar months = 4 since May has 31 days total.
'2022-01-31' and '2022-05-31'
full calendar months = 3 since the month of May is not completed.
I tried subtracting the dates but it gives me the days difference between these dates.
I also tried the function AGE() but it is based also in the days difference, since it is using days to calculate years months etc.
2
Answers
You can generate a full range of days between the two dates as complete months, and then
left join
all the days only between the original dates. This way, you can check if any months are missing days. The query below is formatted as a function for ease of use:See fiddle.
First adjust the two dates to be the first day of the first full month and the last day of the last full month plus one day (
t
CTE) and then calculate the age between the two adjusted dates. Here is an illustration usingsample_data
CTE.