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

soconner
soconner Member
edited October 2023 in Beast Mode

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!

Tagged:

Best Answer

  • ColemenWilson
    ColemenWilson Coach
    edited October 2023 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
    END

    If I solved your problem, please select "yes" above

Answers

  • ColemenWilson
    ColemenWilson Coach
    edited October 2023 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
    END

    If I solved your problem, please select "yes" above

  • soconner
    soconner Member
    edited October 2023

    Thank you!

  • GrantSmith
    GrantSmith Coach

    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!**

Categories