Date conversion functions

If you use BMC Analytics for BSM to report on BMC Remedy ITSM applications, the FN_ADJUSTED_DATE function performs the task of converting database EPOCH dates to human-readable date formats.

FN_ADJUSTED_DATE function

All the date objects in the universe have a formula embedded which looks like the following example for the reported_date from the HPD_HELP_DESK form:

The fn_adjusted_date function performs the following tasks:

• Converts the date from the UNIX Epoch format to a normal date format
• Offsets the date from GMT for the time zone of the BMC Analytics for BSM instance (as defined in the ANA_TIMEZONE_INFO table)
• Returns the result in the date format instead of the Epoch format

The pseudocode for the fn_adjusted_date function follows.

• Function name: fn_adjusted_date
• Parameters: InputDate, number, date in the UNIX Epoch format

• Pseudocode:

  FUNCTION Fn_Adjusted_date (Parameters: Input Date Number)
  BEGIN Function
  BEGIN Function
  Input_GMT_datetime = Parameter input date converted to date format;
  Input_Year = Fetch the Year value from the Input_GMT_datetime
  Input_Year = Fetch the Year value from the Input_GMT_datetime
  IF REGION_FLAG is not set to 'Y' for any region in ANA_TIMEZONE_INFO table
  THEN OutPut_Date = Input_GMT_datetime (default to GMT format)
  THEN OutPut_Date = Input_GMT_datetime (default to GMT format)
  IF REGION_FLAG='Y',
  Fetch DST Start time, DST End time, DST flag,Offset
  from ANA_TIMEZONE_INFO table for a regioncode for which flag='Y'
  from ANA_TIMEZONE_INFO table for a regioncode for which flag='Y'
  Note: DST_Flag='Y' indicates that this region observes Daylight saving time
  Note: DST_Flag='Y' indicates that this region observes Daylight saving time
  IF DST_Flag='Y' THEN
  IF DST_Flag='Y' THEN
  -- IF Input Date falls into DST, add one hour to input gmt time and offset value
  IF Input_GMT_datetime between DST Start time and DST End time
  THEN    
                              OutPut_Date := Input_GMT_datetime + (offset + 1 hour); 
  -- Else add offset value to input gmt time
                   ELSE
                        OutPut_Date := Input_GMT_datetime + offset; 
  END IF;
  ELSE -- IF DST_Flag='N'
  OutPut_Date := Input_GMT_datetime + offset;
               END IF;
  END IF;
  END IF;
  Return Output Date;
  END Function;