no weeks_between date function

[eskewel]

I've noticed that there are the following date functions

• days_between
• months_between
• years_between

but there isn't a weeks_between equivalent. Is this an intentional decision to omit this functionality? I'm having to resort to a personal implementation to get the weeks between 2 dates.

[mtuxgu2]

In addition to @exa-Uwe :

Pay particular attention, if your customer wants to be given the ISO-Weeks-Difference, what could be a total different result:

SELECT '2019-12-29' as startdate, '2020-01-20' as enddate, DAYS_BETWEEN(local.enddate, local.startdate )/7 as days_between_7, week(local.enddate) as iso_endweek, week(local.startdate) as iso_startweek, local.iso_endweek - local.iso_startweek + 52 as possible_iso_week_difference
union all
SELECT '2019-12-30' as startdate, '2020-01-20' as enddate, DAYS_BETWEEN(local.enddate, local.startdate )/7 as days_between_7, week(local.enddate) as iso_endweek, week(local.startdate) as iso_startweek, local.iso_endweek - local.iso_startweek as possible_iso_week_difference
union all
SELECT '2020-01-10' as startdate, '2020-01-20' as enddate, DAYS_BETWEEN(local.enddate, local.startdate )/7 as days_between_7, week(local.enddate) as iso_endweek, week(local.startdate) as iso_startweek, local.iso_endweek - local.iso_startweek as possible_iso_week_difference
union all
SELECT '2015-12-29' as startdate, '2016-01-20' as enddate, DAYS_BETWEEN(local.enddate, local.startdate )/7 as days_between_7, week(local.enddate) as iso_endweek, week(local.startdate) as iso_startweek, local.iso_endweek - local.iso_startweek + 53 as possible_iso_week_difference
union all
SELECT '2015-12-30' as startdate, '2016-01-20' as enddate, DAYS_BETWEEN(local.enddate, local.startdate )/7 as days_between_7, week(local.enddate) as iso_endweek, week(local.startdate) as iso_startweek, local.iso_endweek - local.iso_startweek + 53 as possible_iso_week_difference
union all
SELECT '2016-01-10' as startdate, '2016-01-20' as enddate, DAYS_BETWEEN(local.enddate, local.startdate )/7 as days_between_7, week(local.enddate) as iso_endweek, week(local.startdate) as iso_startweek, local.iso_endweek - local.iso_startweek as possible_iso_week_difference
;

[exa-Uwe]

My personal implementation would be days_between / 7 😁

[eskewel]

So is this 3 or 4 weeks 😁 . Do we use round() or floor()?

Albeit I have a preceding notion from another DB vendor with their 'date difference' implementation. As a comparison the other vendor returns 4 weeks.

select (DAYS_BETWEEN('2020-01-28', '2020-01-01')) as days
		,(DAYS_BETWEEN('2020-01-28', '2020-01-01')/7) as weeks

Days	Weeks
27	3.857142857142857