Dynamically generate last day of prior quarter based on today's date.

Tommy
Member
Hello, I need help generating the last day of the prior quarter based on today's date. This is used to determine which variables should be displayed based on if their date column is between ranges based on the last day of the prior quarter. I have been unsuccessful in my attempts so far. Any help is appreciated.
0
Answers
-
CASE WHEN QUARTER(CURDATE())=1 THEN STR_TO_DATE(CONCAT('12-31-',YEAR(CURDATE())-1), '%m-%d-%Y') WHEN QUARTER(CURDATE())=2 THEN STR_TO_DATE(CONCAT('03-31-',YEAR(CURDATE())), '%m-%d-%Y') WHEN QUARTER(CURDATE())=3 THEN STR_TO_DATE(CONCAT('06-30-',YEAR(CURDATE())), '%m-%d-%Y') WHEN QUARTER(CURDATE())=4 THEN STR_TO_DATE(CONCAT('09-30-',YEAR(CURDATE())), '%m-%d-%Y') END
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1 -
Another version would be
LAST_DAY(CURDATE() - INTERVAL MOD(MONTH(CURDATE()), 3) MONTH)
To break it down:
MONTH(CURDATE()) - returns the month number of the current date (1-12) MOD(..,3) returns the remainder when dividing by the month number by 3 so the last month of the quarter will be 0 INTERVAL .. MONTH - subtracts the number of months to get to the last month of the prior quarter LAST_DAY - returns the last day of the month for the given date
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Categories
- All Categories
- 1.2K Product Ideas
- 1.2K Ideas Exchange
- 1.3K Connect
- 1.1K Connectors
- 273 Workbench
- 2 Cloud Amplifier
- 3 Federated
- 2.7K Transform
- 78 SQL DataFlows
- 525 Datasets
- 2.1K Magic ETL
- 2.9K Visualize
- 2.2K Charting
- 434 Beast Mode
- 22 Variables
- 511 Automate
- 114 Apps
- 389 APIs & Domo Developer
- 8 Workflows
- 26 Predict
- 10 Jupyter Workspaces
- 16 R & Python Tiles
- 332 Distribute
- 77 Domo Everywhere
- 255 Scheduled Reports
- 66 Manage
- 66 Governance & Security
- 1 Product Release Questions
- Community Forums
- 40 Getting Started
- 26 Community Member Introductions
- 68 Community Announcements
- 4.8K Archive