ANA_FISCAL_CALENDAR view
Transaction dates (for example, Base_element_create_date) are stored in the BMC Remedy AR System database in UNIX Epoch Time (number of seconds since January 1, 1970). They are not absolute dates, and there is a time component attached to each of them.
This topic contains the following sections:
Converting transaction dates into local timezone
When joins are created with the ANA_FISCAL_CALENDAR view (which has absolute dates), the fn_adusted_date( ) function is used to convert into the local time zone and truncated to absolute dates to make trend analysis meaningful.
For example, the conversion formula used for a report showing the Purchase Date dimension on a computer running Microsoft SQL Server is as follows:
The conversion formula used for a report showing the Purchase Date dimension on a computer running Oracle is as follows:
The join definition used for Purchase Order.Date_Ordered on a computer running Microsoft SQL Server is as follows:
dbo.fn_adjusted_date(AM_BMC_CORE_BMC_BASEELEMENT.PurchaseDate), 111) AS
DATETIME)),-99)=ASSET_AGE_CALENDAR.Date_Value
The join definition used for Purchase Order.Date_Ordered on a computer running Oracle is as follows:
ASSET_AGE_CALENDAR.Date_Value
ANA_FISCAL_CALENDAR view structure
The following table shows the detailed structure of the ANA_FISCAL_CALENDAR view. Each row in the view represents a date, and each column represents a separate component of the date.
Structure of the ANA_FISCAL_CALENDAR view