I don't know how anyone handles/treats the conversion of a DATE-value to
some sort of corresponding FISCAL-value. However, I found a little
table/data-driven solution on WWW that looked pretty simple (and
elegant?). I also wrote a stored procedure (SP) to handle the
conversion. The SP approach can be superior to a correlated query if a
table contains several dates to be converted and those contain NULL
values, which would not correlate to the PERIODS table.
Anyway, the TABLE, Data, SP Code, and an example are below. It's a bit
to digest, but I think it's decent - useful to me, anyway. If it's is
useful to anyone else, cool, especially since I'm the one usually askin'
f/help from all y'all. Still, If anybody has additional tips/advice,
I'd appreciate it.
Also, tomorrow PM, y'all all tune-in to ESPN and scream for the Tigers,
'cause they R:GR8 ... I just hope they can do it.
Have a nice weekend,
Steve in Memphis
Table: PERIODS Cursor Lock(s)
Descr: Conversion Data for Date-to-Fiscal Values
No. Column Name Attributes
--- ------------------
------------------------------------------------------
1 Period Type : INTEGER NOT NULL
Consrnt: PRIMARY KEY
2 MonthName Type : TEXT 10
3 YearAdjustment Type : INTEGER
4 MonthNumber Type : INTEGER
5 PeriodText Type : TEXT 8
Comment: For Leading Zeroes
6 MonthNumberText Type : TEXT 8
Comment: For leading zeroes.
Current number of rows: 12
Period MonthName YearAdjustment MonthNumber PeriodText
MonthNumberText
------ ------------ ---------------- -------------- ------------
------------------
1 July 1 7 01 07
2 August 1 8 02 08
3 September 1 9 03 09
4 October 1 10 04 10
5 November 1 11 05 11
6 December 1 12 06 12
7 January 0 1 07 01
8 February 0 2 08 02
9 March 0 3 09 03
10 April 0 4 10 04
11 May 0 5 11 05
12 June 0 6 12 06
-- *********************************************************
-- *** SP_GET_FISCAL_DATE.RMD ******************************
-- *********************************************************
-- *** 2008.02.15, JSW *************************************
-- *** To receive a DATE-Value and Desired Return Format
-- *** Convert it using the PERIODS table,
-- *** then Return a STRING-Value in the desired format
-- *** Ex: CALL SP_GET_FISCAL_DATE('12/19/1960',CCYYPP)
-- *** : would return '196106'
-- *********************************************************
SET VAR vFCCYY TEXT =NULL
SET VAR vFPP TEXT =NULL
SET VAR vFReturnValue TEXT =NULL
IF (IFNULL(.vDate2Convert,0,1)) = 1 THEN
SELECT +
((IYR(.vDate2Convert)) + YearAdjustment), +
PeriodText +
INTO +
vFCCYY INDICATOR viFCY, +
vFPP INDICATOR viFP +
FROM +
PERIODS +
WHERE +
((IMON(.vDate2Convert)) = MonthNumber)
ELSE
-- Handle this NULL-value scenario
SET VAR vFormat='NO_DATE'
ENDIF
SWITCH (.vFormat)
CASE 'CCYY'
SET VAR vFReturnValue=.vFCCYY
BREAK
CASE 'PP'
SET VAR vFReturnValue=.vFPP
BREAK
CASE 'CCYYPP'
SET VAR vFReturnValue=(.vFCCYY + .vFPP)
BREAK
CASE 'NO_DATE'
SET VAR vFReturnValue='NO_DATE'
BREAK
DEFAULT
SET VAR vFReturnValue=.vFCCYY
BREAK
ENDSW
RETURN .vFReturnValue
RETURN
-- *** Statement to grab some records from the PROPOSAL table with a
couple of date COL's
-- *** NOTE 1: DATETIME-value converted to DATE-value within CALL() by
use of DEXTRACT()
-- *** NOTE 2: SP called in WHERE-clause operation, again converting
DATETIME with DEXTRACT()
-- *** NOTE 3: By our protocol, the first 2 digits of PROPOSAL_NUMBER
are FISCAL YY.
-- *** : Here, I am looking for mis-matches between the
FY-component in PROPOSAL_NUMBER
-- *** : what it should be based on the value in CREATE_TIMESTAMP.
-- *** NOTE 4: The CVAL('ROWCOUNT') is just a temp/virtual autonum
SELECT (CVAL('ROWCOUNT')),
PROPOSAL_NUMBER,
SEQUENCE_NUMBER,
CREATE_TIMESTAMP,(CALL
GetFiscalDate(DEXTRACT(CREATE_TIMESTAMP),'CCYY')),
REQUESTED_START_DATE_INITIAL, (CALL
GetFiscalDate(DEXTRACT(REQUESTED_START_DATE_INITIAL),'CCYY'))
FROM R_PROPOSAL
WHERE ('20' + (SGET(PROPOSAL_NUMBER,2,1)) ) <> (CALL
GetFiscalDate(DEXTRACT(CREATE_TIMESTAMP),'CCYY'))
J. Stephen Wills
Program Manager, Research Informatics
Office of the Vice Chancellor for Research
University of Tennessee Health Science Center
62 S. Dunlap, Suite 400
Memphis, TN 38163
Office: 901-448-2389
FAX : 901-448-7133
