Skip to content

Instantly share code, notes, and snippets.

@23maverick23

23maverick23/fiscal_date_convert.sql

Last active January 12, 2021 01:00
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save 23maverick23/560804ec39d68893144ac6593968a9f7 to your computer and use it in GitHub Desktop.
Save 23maverick23/560804ec39d68893144ac6593968a9f7 to your computer and use it in GitHub Desktop.
Download ZIP
NS: Fiscal date conversion (Convert calendar date to fiscal date - Return sortable string)
Raw
fiscal_date_convert.sql
-- 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