OBIEE中如何将ddmmyyyy格式日期转换为年、季、月独立字段？

Hey there! As someone new to OBIEE, I get how tricky date handling can feel at first. Let's walk through exactly what you need to do with your Order_date field (formatted as dd/mm/yyyy like 21/11/2017):

1. Convert the dd/mm/yyyy string to a proper Date type #

First, since your Order_date is stored as a string (text) in dd/mm/yyyy format, you'll need to convert it to an OBIEE Date type before doing any extraction. Use the TO_DATE function for this:

TO_DATE("Order_date", 'DD/MM/YYYY')

• The first argument is your date string field.
• The second argument ('DD/MM/YYYY') tells OBIEE the exact format of your input string.

If you specifically need a Date type that represents just the year (e.g., 2017-01-01 for the year 2017), use the TRUNC function to round the date to the start of the year:

TRUNC(TO_DATE("Order_date", 'DD/MM/YYYY'), 'YYYY')

This gives you a Date value pointing to January 1st of the corresponding year.

2. Extract Year, Quarter, Month as separate fields #

Once you have the Date type, you can easily pull out each component using these expressions (add them as calculated fields in your analysis):

Extract Year #

• As a numeric value (e.g., 2017):

  EXTRACT(YEAR FROM TO_DATE("Order_date", 'DD/MM/YYYY'))
  

• As a string (e.g., "2017"):

  TO_CHAR(TO_DATE("Order_date", 'DD/MM/YYYY'), 'YYYY')
  

Extract Quarter #

• As a numeric value (1-4):

  EXTRACT(QUARTER FROM TO_DATE("Order_date", 'DD/MM/YYYY'))
  

• As a string (e.g., "Q3" or just "3"):

  TO_CHAR(TO_DATE("Order_date", 'DD/MM/YYYY'), 'Q') -- Returns "1", "2", "3", "4"
  -- Or for "Q1" style:
  'Q' || TO_CHAR(TO_DATE("Order_date", 'DD/MM/YYYY'), 'Q')
  

Extract Month #

• As a numeric value (1-12):

  EXTRACT(MONTH FROM TO_DATE("Order_date", 'DD/MM/YYYY'))
  

• As a two-digit string (e.g., "11" for November):

  TO_CHAR(TO_DATE("Order_date", 'DD/MM/YYYY'), 'MM')
  

• As a month name abbreviation (e.g., "NOV"):

  TO_CHAR(TO_DATE("Order_date", 'DD/MM/YYYY'), 'MON')
  

• As a full month name (e.g., "NOVEMBER"):

  TO_CHAR(TO_DATE("Order_date", 'DD/MM/YYYY'), 'MONTH')
  

A quick tip: If you're going to reuse the converted Date in multiple calculated fields, it's better to create a single calculated field for the converted Date first (like Converted_Order_Date = TO_DATE("Order_date", 'DD/MM/YYYY')), then reference that in your extraction formulas to avoid repeating code.

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