Domo Ideas Exchange  Beast Modes  First / Last Days of the Month / Week
Problem:
How Do I calculate the start or end date of the week or month?
Solution:
Last Day of the Month:
This is fairly simple as we can use utilize the LAST_DAY
function which will return the last day of the month for the provided date.
LAST_DAY(`dt`)
First Day of the Month:
For this we can do some simple math to subtract a specific number of days. We need to get back to day one. The DAYOFMONTH
function will return the day number of the supplied date. We just need to subtract one less from the date's day number
 Author:  Created:  Last Modified:  Description: Subtract the number of days of the date (except one) to get the first of the month  24(241) => day 24  23 days = day 1 DATE_SUB(`dt`, INTERVAL (DAYOFMONTH(`dt`)  1) DAY)
Last Day of the Week:
Similar to the first day of the month we can utilize math to add a specific number of days to get to Saturday. There is a function called DAYOFWEEK
which will return a number between 1 (Sunday) and 7 (Saturday). We simply need to calculate the difference from the given date to Saturday and add that number of days.
 Author:  Created:  Last Modified:  Description: Get the last day of the week.  This is done by getting the day of the week (17) and adding the number  of days needed to equal 7 (Saturday) DATE_ADD(`dt`, INTERVAL (7 DAYOFWEEK(`dt`)) DAY)
First Day of the Week:
For the First day of the week we do the opposite of the Last Day of the Week and subtract days until we get to day 1 of the week.
 Author:  Created:  Last Modified:  Description: Get the first day of the week  This is done by subtracting the day number (17) from the given date.  5 (Thursday)  (51) days => 5  4 = 1 (Sunday) DATE_SUB(`dt`, INTERVAL (DAYOFWEEK(`dt`)  1) DAY)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**
Comments

@GrantSmith What if you would like to have Monday as the first day of the week?
0 
Hi @Derreck
You can just add a day to the date:
DATE_ADD(DATE_SUB(`dt`, INTERVAL (DAYOFWEEK(`dt`)  1) DAY), INTERVAL 1 DAY)
End of the week:
DATE_ADD(`dt`, INTERVAL (7 DAYOFWEEK(`dt`) + 1) DAY)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2 
2

How to get a last day of the next month?
0 
LAST_DAY(DATE_ADD(`dt`, INTERVAL 1 MONTH))
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 
Thanks Grant! This was very helpful.
0
Categories
 10.6K All Categories
 1 APAC User Group
 12 Welcome
 36 Domo News
 9.6K Using Domo
 1.9K Dataflows
 2.4K Card Building
 2.2K Ideas Exchange
 1.2K Connectors
 339 Workbench
 251 Domo Best Practices
 11 Domo Certification
 461 Domo Developer
 47 Domo Everywhere
 100 Apps
 703 New to Domo
 84 Dojo
 Domopalooza
 1.1K 日本支部
 4 道場日本支部へようこそ
 22 お知らせ
 63 Kowaza
 296 仲間に相談
 649 ひらめき共有