How to convert date to text with this format 'YYYY-MM-DD"?

JobDeLuna

Hi,

Anyone can help on how to convert date to text with this format 'YYYY-MM-DD"?

JoseP-Oracle

You can try this formula:

cast(year("Snapshot Date") as CHAR(4)) || '-' || cast(month("Snapshot Date")as CHAR(2)) || '-' || cast(day("Snapshot Date")as CHAR(2))

JobDeLuna

Hi, @JoseP-Oracle .

Thank you for your response; however, it's giving me single digits for months and days from 1 to 9 even though we have set CHAR(2)

I may have a problem with sorting.

JoseP-Oracle

@JobDeLuna - Before fixing the expression to your need, just want to confirm why the need to covert the date to text. If for display format at the report, you can configure a desired display format in the visualization -> Properties -> Date / Time Format -> Format for the column. i.e. you can set the format in there as YYYY-MM-DD

Madasamy -Oracle

I just tweaked that formula a bit and it works this way. Is this how you want to display your dates in text format?

cast(year("PC0"."Time"."Date") as CHAR(4)) || '-' || cast(MONTHNAME("PC0"."Time"."Date") as CHAR(3)) || '-' || cast(DAY("PC0"."Time"."Date") as CHAR(2))

And this is how it looks for a whole month!