Categories
Cast a Date field as CHAR but alter the format.

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"
Answers
-
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
0 -
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
0 -
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
0