Cast a Date field as CHAR but alter the format.

Question

Hi all,

I have a Case Statement that displays a date field when a Fixed Choice List  is NULL. To achieve this I have had to CAST the date field as CHAR, however the returned value shows in the wrong date format.

Is there a way to format the date?

Formula :

CASE WHEN "CommercialContractLaunch_c"."FieldAlert1IssuedSupportingInfo_c_Meaning" IS NULL THEN CAST("CommercialContractLaunch_c"."FieldAlert1Issued_c" AS CHAR) ELSE "CommercialContractLaunch_c"."FieldAlert1IssuedSupportingInfo_c_Meaning" END

Date field : "CommercialContractLaunch_c"."FieldAlert1Issued_c"

Fixed Choice List : "CommercialContractLaunch_c"."FieldAlert1IssuedSupportingInfo_c_Meaning"

Binod Kumar · Answer

Remove the text from template for date column and in DM use below systax

TO_CHAR(sysdate,'DD/MM/YYYY','NLS_DATE_LANGUAGE = american')  or any format you need

Rich Carr · Answer

Hi,

I have managed to solve my issue via the use of SUBSTRING and CONCAT.

******

CASE

WHEN "CommercialContractLaunch_c"."FieldAlert1IssuedSupportingInfo_c_Meaning" IS NULL

THEN CONCAT(CONCAT( CONCAT(SUBSTRING(CAST("CommercialContractLaunch_c"."FieldAlert1Issued_c" AS CHAR),9,2), '/'), CONCAT(SUBSTRING(CAST("CommercialContractLaunch_c"."FieldAlert1Issued_c" AS CHAR),6,2), '/')),   SUBSTRING(CAST("CommercialContractLaunch_c"."FieldAlert1Issued_c" AS CHAR),1,4))

ELSE

"CommercialContractLaunch_c"."FieldAlert1IssuedSupportingInfo_c_Meaning"

END

*****

A little more longwinded than I'd of liked but it works as intended.

Thank you

Dimple-Nagesh-Support-Oracle · Answer

Hi  User_JRMG3,

Did you try CAST("CommercialContractLaunch_c"."FieldAlert1Issued_c" AS DATE) this will show only Date without Time

Else you can edit the column Properties -- Data Format -- Override current Format --Choose the required format and Save it -- Click on Results and test the data.

Hope this helps.

Thanks

Dimple N