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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
8 | |
7 | |
5 | |
4 | |
4 | |
4 | |
4 | |
3 | |
3 | |
3 |