Get the number of weeks and days between two dates

MarkSnodgrass

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?

GrantSmith

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

 

GrantSmith

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.

GrantSmith

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

 

MarkSnodgrass

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

 

jaeW_at_Onyx

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.