19-06-2020 11:15 AM
I've noticed that there are the following date functions
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.
20-06-2020 08:13 AM
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
;
19-06-2020 12:23 PM
My personal implementation would be days_between / 7 😁
22-06-2020 12:39 PM - edited 22-06-2020 12:40 PM
22-06-2020 12:37 PM
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 |
Then it's time to become part of a unique family! Discover helpful tips and support other Community members with your knowledge.
Sign In