Cast a Date field as CHAR but alter the format.

User_JRMG3

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"

Dimple-Nagesh-Support-Oracle

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

User_JRMG3

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

Binod Kumar

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