Skip to content

    • Beast Mode

    Beast Mode

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

    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:

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answer

    • 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

    • 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

    • Member
      edited October 2023

      Thank you!

    • Coach

      A simplified Beast mode would be something like:

      1. `Start date` - INTERVAL DAYOFMONTH(`Start date`) DAY + 1 - INTERVAL MOD(MONTH(`Start date`)+2, 3) MONTH

      Detailed Breakdown:

      1. `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

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

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories