Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
Former Member
5,262

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

1 Comment
Labels in this area