Diff function for date does not work

llyly32 Registered Posts: 3 ✭✭✭

Hi, I'm usind the Diff function to calculate a difference between two dates and I want the formula to return me the difference in weeks.

This formula works well for almost all the cases but for the case when there is only 1 week between two dates (for example 2021/41 and 2021/42) the function returns me 0 instead of 1.

I don't know how to face this issue. Can you help me ? Maybe it's a problem of parameters ?


Best Answer

  • Marine
    Marine Dataiker, Registered Posts: 29 Dataiker
    Answer ✓

    Hi @llyly32

    Thanks for these precisions, now I get it!

    The 'diff" function returns only whole week count, namely 7 days. It does not count incomplete weeks with less than 7 days. To achieve your expected outcome, you should make sure that the days are at the start of the week. The "trunc" function, which rounds a date to the first day of the specified time unit, can help you compute what you want :

    diff(trunc('2021-10-25', "weeks"), trunc('2021-10-21',"weeks"), 'weeks')


Setup Info
      Help me…