Convert Date Field to "YYYY-Qx" Format

Question

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

Gianni Ceresa · Answer

@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.

Bhaskar Konar · Answer

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)

Hope this help.

Thank you.

Gianni Ceresa · Answer

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…