Get the number of weeks and days between two dates

I am needing to find the number of weeks and then the number of leftover days between two dates. I am open to doing this in our on-premise MS SQL, or in Magic ETL or in Redshift or MySQL, whichever is easiest. 

If I have dates of 8/1/2020 and 8/9/2020, for example, my expected result would be 1 week and 1 day. 

When I am trying MSSQL and using DATEDIFF(week, startdate,enddate) it gives me a result of 2 weeks. Not what I am wanting. 

I will need the end result to be two values: number of weeks, number of days (remaining days after the number of weeks, if there are any)

Any suggestions?

**Check out my Domo Tips & Tricks Videos

**Make sure to <3 any users posts that helped you.
**Please mark as accepted the ones who solved your issue.

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    @MarkSnodgrass 

     

    Alternatively you can utilize DATEDIFF but by default it includes the current date as a whole day so depending on how your business rules are you may want to -1 day from your date difference to not calculate today.

     

    Week:

    DATEDIFF(`EndDate`, `StartDate`) / 7

     

    Days:

    MOD(DATEDIFF(`EndDate`, `StartDate`), 7)

     

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • Hey @MarkSnodgrass 

    Any time I'm doing date differences I like to utilize unix_timestamp. It converts everything to an integer of the number of seconds since 1970-01-01. This makes working with the data easier.

     

    DATEDIFF is returning your difference in the whole unit you specified so you don't get the fractional aspect of it.

     

    Some simple math converting seconds will get you what you want.

     

    See also https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Date-Calculations-minutes-and-seconds/m-p/49259/highlight/true#M8465

     

    Weeks:

     

    -- 604,800 seconds in a week
    -- ROUND & -0.5 to truncate / drop the decimals
    ROUND((UNIX_TIMESTAMP(`EndTime`) - UNIX_TIMESTAMP(`StartTime`)) / 604800 - 0.5, 0)

     

     

    Days:

     

     

    ROUND(MOD((UNIX_TIMESTAMP(`StartDate`) - UNIX_TIMESTAMP(`EndDate`)) / 86400, 7) - 0.5, 0)

     

     

     

    You can just do a CONCAT to join them together in the format you wish.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • GrantSmith
    GrantSmith Coach
    Answer ✓

    @MarkSnodgrass 

     

    Alternatively you can utilize DATEDIFF but by default it includes the current date as a whole day so depending on how your business rules are you may want to -1 day from your date difference to not calculate today.

     

    Week:

    DATEDIFF(`EndDate`, `StartDate`) / 7

     

    Days:

    MOD(DATEDIFF(`EndDate`, `StartDate`), 7)

     

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Thanks @GrantSmith ! This worked great. I did the calculations in MS SQL, which doesn't have the MOD function, but it does a similar which just uses the percent sign. It ended up looking like this for the remaining days calculation.

    DATEDIFF(DAY,`EndDate`, `StartDate`)%7

     

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • just FYI i believe that mathematical operation is called "Modulo" or "Modulus"

    https://www.w3schools.com/sql/func_mysql_mod.asp

    https://docs.microsoft.com/en-us/sql/t-sql/language-elements/modulo-transact-sql?view=sql-server-ver15

     

    @GrantSmith  i noticed you wrapped in Round , 0 to get rid of trailing decimal places.  I guess it's six of one , but in my mind i prefer CAST as INT b/c it actually forces the data type to be a narrower data type instead of relying on Domo to guess that a column that is NUMERIC / DECIMAL COULD BE compressed to an INT.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"