Get Current Quarter Beginning Date, Last Quarter Beginning Date, and Next Quarter Beginning Date

[Former Member]

Hello,

Find below SAP HANA SQL Script to get Current Quarter Beginning Date, Last Quarter Beginning Date, and Next Quarter Beginning Date.

Suppose you have business requirements based on some date column you need to do some manipulation or calculation based on Current Quarter Beginning Date, Last Quarter Beginning Date, or Next Quarter Beginning Date. In below example you can Change CURRENT_DATE From actual table date field and DUMMY with your actual table according to your requirements, for example i took dummy and current_date.

SELECT * FROM (SELECT CASE WHEN (SUBSTRING (QUARTER (TO_DATE(CURRENT_DATE, 'YYYY-MM-DD'), 1),6,2) = 'Q1')

  THEN TO_DATE(YEAR(CURRENT_DATE))

            WHEN (SUBSTRING (QUARTER (TO_DATE(CURRENT_DATE, 'YYYY-MM-DD'), 1),6,2) = 'Q2')

            THEN ADD_MONTHS(TO_DATE(YEAR(CURRENT_DATE), 'YYYY'),3)

            WHEN (SUBSTRING (QUARTER (TO_DATE(CURRENT_DATE, 'YYYY-MM-DD'), 1),6,2) = 'Q3')

            THEN ADD_MONTHS(TO_DATE(YEAR(CURRENT_DATE), 'YYYY'),6)

            WHEN (SUBSTRING (QUARTER (TO_DATE(CURRENT_DATE, 'YYYY-MM-DD'), 1),6,2) = 'Q4')

            THEN ADD_MONTHS(TO_DATE(YEAR(CURRENT_DATE), 'YYYY'),9)

            ELSE CURRENT_DATE

            END AS Quarter_Begining_Date

  FROM DUMMY),

  (SELECT CASE WHEN (SUBSTRING (QUARTER (TO_DATE(CURRENT_DATE, 'YYYY-MM-DD'), 1),6,2) = 'Q1')

  THEN ADD_MONTHS(TO_DATE(YEAR(CURRENT_DATE)),-3)

            WHEN (SUBSTRING (QUARTER (TO_DATE(CURRENT_DATE, 'YYYY-MM-DD'), 1),6,2) = 'Q2')

            THEN ADD_MONTHS(TO_DATE(YEAR(CURRENT_DATE), 'YYYY'),-6)

            WHEN (SUBSTRING (QUARTER (TO_DATE(CURRENT_DATE, 'YYYY-MM-DD'), 1),6,2) = 'Q3')

            THEN ADD_MONTHS(TO_DATE(YEAR(CURRENT_DATE), 'YYYY'),-9)

            WHEN (SUBSTRING (QUARTER (TO_DATE(CURRENT_DATE, 'YYYY-MM-DD'), 1),6,2) = 'Q4')

            THEN ADD_MONTHS(TO_DATE(YEAR(CURRENT_DATE), 'YYYY'),-12)

            ELSE CURRENT_DATE

            END AS Last_Quarter_Begining_Date

  FROM DUMMY),

  (SELECT CASE WHEN (SUBSTRING (QUARTER (TO_DATE(CURRENT_DATE, 'YYYY-MM-DD'), 1),6,2) = 'Q1')

  THEN ADD_MONTHS(TO_DATE(YEAR(CURRENT_DATE), 'YYYY'),3)

            WHEN (SUBSTRING (QUARTER (TO_DATE(CURRENT_DATE, 'YYYY-MM-DD'), 1),6,2) = 'Q2')

            THEN ADD_MONTHS(TO_DATE(YEAR(CURRENT_DATE), 'YYYY'),6)

            WHEN (SUBSTRING (QUARTER (TO_DATE(CURRENT_DATE, 'YYYY-MM-DD'), 1),6,2) = 'Q3')

            THEN ADD_MONTHS(TO_DATE(YEAR(CURRENT_DATE), 'YYYY'),9)

            WHEN (SUBSTRING (QUARTER (TO_DATE(CURRENT_DATE, 'YYYY-MM-DD'), 1),6,2) = 'Q4')

            THEN ADD_MONTHS(TO_DATE(YEAR(CURRENT_DATE), 'YYYY'),12)

            ELSE CURRENT_DATE

            END AS Next_Quarter_Begining_Date

  FROM DUMMY);

Thanks