# Get the number of weeks and days between two dates

Coach

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 any users posts that helped you.

• Coach

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)`

**Did this solve your problem? Accept it as a solution!**

• Coach

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.

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.

**Did this solve your problem? Accept it as a solution!**
• Coach

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)`

**Did this solve your problem? Accept it as a solution!**
• Coach

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 any users posts that helped you.
• Coach

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"