Skip to Content

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

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Rajesh Rana

    giving error while running

     

    1). [SAP AG][LIBODBCHDB32 DLL][HDBODBC32] Syntax error or access violation;257 sql syntax error: incorrect syntax near “-“: line 1 col 81 ‘Received Alerts’ (OAIB) (at pos 81)

     

     

    (0) 

Leave a Reply