Extract Quarter Value from Date Field and Return First Date of that Quarter

Hello,
I need to extract the quarter from a date so it returns that quarter it falls into (example: 01/01/2023 falls into Q1.
Instead of Q1 I want it to return the first day of that quarter.
Example:
DATE
01/05/2023
QTR DATE
01/01/2023
To note I am making a beast mode to do this in a view.
I have already tried to use DATE and DATE_FORMAT with no luck
DATE attempt:
DATE(QUARTER(`DATE'))
DATE_FORMAT attempt
DATE_FORMAT(QUARTER(`DATE', '%m-%d-%Y')
Any help appreciated, thank you!
Best Answer
-
CASE
WHEN MONTH(`DATE`) = 1 THEN LAST_DAY(`DATE`) + INTERVAL 1 DAY - INTERVAL 1 MONTH
WHEN MONTH(`DATE`) = 2 THEN LAST_DAY(`DATE`) + INTERVAL 1 DAY - INTERVAL 2 MONTH
WHEN MONTH(`DATE`) = 3 THEN LAST_DAY(`DATE`) + INTERVAL 1 DAY - INTERVAL 3 MONTH
WHEN MONTH(`DATE`) = 4 THEN LAST_DAY(`DATE`) + INTERVAL 1 DAY - INTERVAL 1 MONTH
WHEN MONTH(`DATE`) = 5 THEN LAST_DAY(`DATE`) + INTERVAL 1 DAY - INTERVAL 2 MONTH
WHEN MONTH(`DATE`) = 6 THEN LAST_DAY(`DATE`) + INTERVAL 1 DAY - INTERVAL 3 MONTH
WHEN MONTH(`DATE`) = 7 THEN LAST_DAY(`DATE`) + INTERVAL 1 DAY - INTERVAL 1 MONTH
WHEN MONTH(`DATE`) = 8 THEN LAST_DAY(`DATE`) + INTERVAL 1 DAY - INTERVAL 2 MONTH
WHEN MONTH(`DATE`) = 9 THEN LAST_DAY(`DATE`) + INTERVAL 1 DAY - INTERVAL 3 MONTH
WHEN MONTH(`DATE`) = 10 THEN LAST_DAY(`DATE`) + INTERVAL 1 DAY - INTERVAL 1 MONTH
WHEN MONTH(`DATE`) = 11 THEN LAST_DAY(`DATE`) + INTERVAL 1 DAY - INTERVAL 2 MONTH
WHEN MONTH(`DATE`) = 12 THEN LAST_DAY(`DATE`) + INTERVAL 1 DAY - INTERVAL 3 MONTH
ENDIf I solved your problem, please select "yes" above
0
Answers
-
CASE
WHEN MONTH(`DATE`) = 1 THEN LAST_DAY(`DATE`) + INTERVAL 1 DAY - INTERVAL 1 MONTH
WHEN MONTH(`DATE`) = 2 THEN LAST_DAY(`DATE`) + INTERVAL 1 DAY - INTERVAL 2 MONTH
WHEN MONTH(`DATE`) = 3 THEN LAST_DAY(`DATE`) + INTERVAL 1 DAY - INTERVAL 3 MONTH
WHEN MONTH(`DATE`) = 4 THEN LAST_DAY(`DATE`) + INTERVAL 1 DAY - INTERVAL 1 MONTH
WHEN MONTH(`DATE`) = 5 THEN LAST_DAY(`DATE`) + INTERVAL 1 DAY - INTERVAL 2 MONTH
WHEN MONTH(`DATE`) = 6 THEN LAST_DAY(`DATE`) + INTERVAL 1 DAY - INTERVAL 3 MONTH
WHEN MONTH(`DATE`) = 7 THEN LAST_DAY(`DATE`) + INTERVAL 1 DAY - INTERVAL 1 MONTH
WHEN MONTH(`DATE`) = 8 THEN LAST_DAY(`DATE`) + INTERVAL 1 DAY - INTERVAL 2 MONTH
WHEN MONTH(`DATE`) = 9 THEN LAST_DAY(`DATE`) + INTERVAL 1 DAY - INTERVAL 3 MONTH
WHEN MONTH(`DATE`) = 10 THEN LAST_DAY(`DATE`) + INTERVAL 1 DAY - INTERVAL 1 MONTH
WHEN MONTH(`DATE`) = 11 THEN LAST_DAY(`DATE`) + INTERVAL 1 DAY - INTERVAL 2 MONTH
WHEN MONTH(`DATE`) = 12 THEN LAST_DAY(`DATE`) + INTERVAL 1 DAY - INTERVAL 3 MONTH
ENDIf I solved your problem, please select "yes" above
0 -
Thank you!
0 -
A simplified Beast mode would be something like:
`Start date` - INTERVAL DAYOFMONTH(`Start date`) DAY + 1 - INTERVAL MOD(MONTH(`Start date`)+2, 3) MONTH
Detailed Breakdown:
`Start date` - INTERVAL DAYOFMONTH(`Start date`) DAY + 1
Returns the first day of the month for a date with start date. DAYOFMONTH returns the day number of the month, subtracting it would get the last day of the prior month so we add an additional day to get the first of the month
- INTERVAL MOD(MONTH(`Start date`)+2, 3) MONTH
This causes the months to return a sequence of 0, 1,2,0,1,2… to subtract that number of months.
MONTH returns the month number, +2 to shift the sequence right 2 places so that 1 = 0, otherwise it'll return 2,1,0,2,1,0…
MOD(…,3) takes the month number and divides it by 3 and returns the remainder.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 310 Workbench
- 7 Cloud Amplifier
- 9 Federated
- 3K Transform
- 113 SQL DataFlows
- 650 Datasets
- 2.2K Magic ETL
- 4K Visualize
- 2.5K Charting
- 792 Beast Mode
- 78 App Studio
- 44 Variables
- 754 Automate
- 187 Apps
- 479 APIs & Domo Developer
- 71 Workflows
- 17 DomoAI
- 40 Predict
- 17 Jupyter Workspaces
- 23 R & Python Tiles
- 407 Distribute
- 118 Domo Everywhere
- 279 Scheduled Reports
- 10 Software Integrations
- 141 Manage
- 137 Governance & Security
- 8 Domo Community Gallery
- 47 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 113 Community Announcements
- 4.8K Archive