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
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 620 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 742 Beast Mode
- 58 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive