no weeks_between date function

SQL-Fighter

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.

4 REPLIES 4

Contributor

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
;

flameshot-200620-081117.png

 

Moderator
Moderator

My personal implementation would be days_between / 7 :beaming_face_with_smiling_eyes:

SQL-Fighter
 

SQL-Fighter

So is this 3 or 4 weeks :beaming_face_with_smiling_eyes: . 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

 

 

DaysWeeks
273.857142857142857