Oracle Business Intelligence Applications

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Business Intelligence Applications

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Convert Date Field to "YYYY-Qx" Format

Received Response
12
Views
3
Comments
aayhindujaswiss
aayhindujaswiss Rank 2 - Community Beginner
in Oracle Business Intelligence Applications

Summary
Need to convert a date field from MM/DD/YYYY to "YYYY-Qx" format.

Content
Hi,
I’m trying to convert a date field from the standard MM/DD/YYYY format into a "YYYY-Qx" format (e.g., "2020-Q1").

I’ve tried using Calendar functions in formulas. While they help extract the year and quarter separately—one column for the year (e.g., "2020") and another for the quarter number (e.g., "1")—I run into trouble when trying to concatenate these into a single column.

When I try to use Concat or a similar function to combine them (like "2020-Q1"), I get a syntax error. It seems the platform doesn't like combining formula-driven fields.

Has anyone successfully done this or found a workaround? Any help would be greatly appreciated!

Thanks,
Stephanie

Tagged:

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

    Welcome to the Oracle Analytics community and forum.

    What was the error you got when concatenating the 3 pieces (formula for year, static text, formula for quarter)?

    If you provide that, there is maybe a simple fix in the syntax…

  • Bhaskar Konar
    Bhaskar Konar Rank 8 - Analytics Strategist
    edited Jun 26, 2025 5:07PM
    https://community.oracle.com/products/oracleanalytics/discussion/26333/convert-date-field-to-yyyy-qx-format

    Hi Stephanie,

    If you are facing the issue while concatenation, you have to cast Year or Quarter to character before the concatenation.

    Here is a sample code using 'Expenses - Expense Transactions Real Time' Subject Area. which is working for me.

    CAST(YEAR("Time"."Date") AS CHAR)|| '-Q' ||CAST(QUARTER_OF_YEAR("Time"."Date") AS CHAR)
    image.png

    Hope this help.

    Thank you.

  • Gianni Ceresa
    Gianni Ceresa Mod

    @User_TUN0Q ,

    If the error you had was something like [nQSError: 22020] Function Concat does not support non-text types. then the expression you need to use is not exactly what is posted above.

    As you see in screenshot just casting as char will generate char values longer than needed, adding a space after the year and also a space after the quarter number.

    When you cast a value to a different type you should be as precise as possible on the type. The expression you look for is:

    CAST(YEAR("Time"."T00 Calendar Date") as CHAR(4)) || '-Q' || CAST(QUARTER_OF_YEAR("Time"."T00 Calendar Date") as CHAR(1))

    The year is only 4 characters, therefore the cast is to a CHAR(4), and the quarter number is a single character, therefore a cast to CHAR(1). This is to avoid adding white spaces after both numbers.