Oracle Transactional Business Intelligence

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Cast a Date field as CHAR but alter the format.

Received Response
367
Views
3
Comments

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"

Tagged:

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

  • User_JRMG3
    User_JRMG3 Rank 1 - Community Starter

    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
    Binod Kumar Rank 1 - Community Starter

    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