关于在Informatica表达式转换中将localtimestamp格式日期转换为HHMISSMS格式的技术问询

To convert your input timestamp string 30-oct-21 12.06.50.666340700 AM into the target HHMISSMS format (no colons/spaces, expected output: 12065066), use this combined Informatica expression:

TO_CHAR(TO_TIMESTAMP(input_date_column, 'DD-mon-RR HH.MI.SS.FF9 AM'), 'HHMISSFF2')

Breakdown of the Expression: #

• Step 1: Parse String to Timestamp
  TO_TIMESTAMP(input_date_column, 'DD-mon-RR HH.MI.SS.FF9 AM')
  This function turns your raw string into a proper TIMESTAMP data type. The format mask aligns perfectly with your input structure:

  • DD: 2-digit day (30)
  • mon: 3-letter month abbreviation (oct)
  • RR: 2-digit year (21 maps to 2021)
  • HH: 12-hour format hour (keeps the 12 from 12 AM instead of converting to 00)
  • MI: 2-digit minutes (06)
  • SS: 2-digit seconds (50)
  • FF9: Captures the full 9-digit millisecond value (.666340700)
  • AM: Recognizes the AM/PM time indicator

• Step 2: Format Timestamp to Target String
  TO_CHAR(..., 'HHMISSFF2')
  This converts the parsed timestamp into your desired compact format:

  • HH: Retains the 12-hour hour value
  • MI: 2-digit minutes
  • SS: 2-digit seconds
  • FF2: Extracts only the first 2 digits of the millisecond value (66 from .666340700)

Example Test: #

When fed the input 30-oct-21 12.06.50.666340700 AM, this expression will output exactly 12065066—matching your expected result.

Optional: Handle NULL Values #

If your input column might have NULLs, wrap the expression with NVL to avoid errors:

NVL(TO_CHAR(TO_TIMESTAMP(input_date_column, 'DD-mon-RR HH.MI.SS.FF9 AM'), 'HHMISSFF2'), '')

内容的提问来源于stack exchange，提问作者Sanju