How to create a field and convert "Invoice Date" 2022-06-21 to 202206?

[rdasilva]

Hello everyone,  

I would like to create a field called "Fiscal Period" and using data from an existing field called  "INVOICE_DATE_PK" .The data from "INVOICE_DATE_PK" field is showing as 2022.06.21 and I would like the data from the newly created field Fiscal Period showing as 202206.

Thank you in advance for the help. 🙂

[DataNath]

How does this look @rdasilva? Expression is:

Replace(Left([INVOICE_DATE_PK], 7),'.','')

[rdasilva]

Hello there, 

It did not work. Please see below. 

[DataNath · Accepted solution]

Oh sorry, I didn't realise you were pulling this from a database. When using the In-DB tools, you have to stick to the correct SQL Syntax. I'm not massively experienced but shouldn't there be a SELECT beforehand? And you may perhaps need to convert the incoming field to a string type (to use the replace/left functions) if it's originally being processed as a Date.

[rdasilva]

Thank you for the help!