Last active
January 12, 2021 01:00
-
-
Save 23maverick23/560804ec39d68893144ac6593968a9f7 to your computer and use it in GitHub Desktop.
NS: Fiscal date conversion (Convert calendar date to fiscal date - Return sortable string)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Convert calendar date to Oracle fiscal month (MM-MON) | |
CASE WHEN MOD(TO_NUMBER(TO_CHAR({date_field}, 'MM')), 12) >= 6 THEN TO_CHAR(MOD(TO_NUMBER(TO_CHAR({date_field}, 'MM')), 12) - 5, '09') ELSE TO_CHAR(MOD(TO_NUMBER(TO_CHAR({date_field}, 'MM')), 12) + 7, '09') END || '-' || TO_CHAR({date_field}, 'MON') | |
-- Convert calendar date to Oracle fiscal quarter (Q#) | |
CASE WHEN TO_CHAR({date_field}, 'MM') IN ('06', '07', '08') THEN 'Q1' WHEN TO_CHAR({date_field}, 'MM') IN ('09', '10', '11') THEN 'Q2' WHEN TO_CHAR({date_field}, 'MM') IN ('12', '01', '02') THEN 'Q3' WHEN TO_CHAR({date_field}, 'MM') IN ('03', '04', '05') THEN 'Q4' END | |
-- Convert calendar date to Oracle fiscal year (YYYY) | |
CASE WHEN TO_NUMBER(TO_CHAR({date_field}, 'MM')) < 6 THEN TO_CHAR(TO_NUMBER(TO_CHAR({date_field}, 'YYYY')) - 1, '9999') ELSE TO_CHAR(TO_NUMBER(TO_CHAR({date_field}, 'YYYY')), '9999') END |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment