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

soconner

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!

ColemenWilson

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
END

ColemenWilson

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
END

soconner

Thank you!

GrantSmith

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.