Get last, current & next quarters' begin and end dates without conditional checks

[former_member559998]

Hello All,

As I was exploring for a need to get begin and end dates, I happened to stumble across blog, which gives information on how to derive begin & end dates of current & next quarters; based on learning from this blog, I thought of deriving these dates without case statement.

I have to admit that since the data that is being derived is just one row, skipping the case-statement gives only an improvement of few micro-seconds. However, I would like to highlight how interesting the date functions are.

SELECT 	CURRENT_DATE 																								DATE_OF_INTEREST,

		ADD_MONTHS(TO_DATE(YEAR(CURRENT_DATE)),(TO_INT(RIGHT(QUARTER(CURRENT_DATE),1)-2) * 3)) 						LAST_QTR_BEGIN,

		ADD_DAYS(ADD_MONTHS(TO_DATE(YEAR(CURRENT_DATE)),(TO_INT(RIGHT(QUARTER(CURRENT_DATE),1)-1) * 3)),-1) 		LAST_QTR_END,

		ADD_MONTHS(TO_DATE(YEAR(CURRENT_DATE)),(TO_INT(RIGHT(QUARTER(CURRENT_DATE),1)-1) * 3)) 						CURRENT_QTR_BEGIN,

		ADD_DAYS(ADD_MONTHS(TO_DATE(YEAR(CURRENT_DATE)),TO_INT(RIGHT(QUARTER(CURRENT_DATE),1) * 3)),-1) 			CURRENT_QTR_END,

		ADD_MONTHS(TO_DATE(YEAR(CURRENT_DATE)),TO_INT(RIGHT(QUARTER(CURRENT_DATE),1) * 3)) 							NEXT_QTR_BEGIN,

		ADD_DAYS(ADD_MONTHS(TO_DATE(YEAR(CURRENT_DATE)),(TO_INT(RIGHT(QUARTER(CURRENT_DATE),1)+1) * 3)),-1) 		NEXT_QTR_END		

	FROM DUMMY;

 

 

The tricky situations, I thought, would be the dates that fall in last quarter of an year, to get next quarter info., and dates that fall in first quarter of an year, to get previous quarter info.

I did test the above code for dates 2018-02-28 & 2018-11-25 and I saw the results as expected.

 

Current date as reference:



First quarter's date as reference:



Last quarter's date as reference:



 

I have tried using M_TIME_DIMENSION to derive the above and it is not better in anyway.

Please feel free to highlight any limitations/bugs with the above code, in case, I haven't realized in my usage.

 

Thank you.

Regards,
Shashidhar Nagavelli