Oracle Analytics Cloud and Server

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

Concatenating Dates

Received Response
11
Views
2
Comments
Chris Arnold
Chris Arnold Rank 5 - Community Champion

I want to show a date range in one field -- such as '4/18/2016 - 4/25/2016' on a report.

The date columns I am using are actually DateTime so I know I need to cast them as Date.

CAST( "Inv"."Date Entered") as DATE

CAST( "Inv"."Date Sent") as DATE

Also I know I cannot concat 2 dates so I need to cast them as CHAR. So here is the code I am using:

CAST(CAST( "Inv"."Date Entered") as DATE) as CHAR)||' - '||CAST(CAST( "Inv"."Date Sent") as DATE) as CHAR)

The problem is...the above code returns the following in my report:

'04/18/2016 00:00:00 - 04/25/2016 00:00:00'


It seems that by casting the dates to CHAR, it returns the TIME information.

Is there a way I can concat these 2 date fields & NOT show the TIME information?


Any help is greatly appreciated.

Answers

  • plyskovich
    plyskovich Rank 3 - Community Apprentice

    Hi,

    Can you try this:

    Evaluate('TO_CHAR(%1 ,%2)' AS CHAR ,"Creation Date"."Creation DateTime",'DD-MON-YY hh:mi AM')

  • Chris Arnold
    Chris Arnold Rank 5 - Community Champion

    That worked. I went with:

    Evaluate('TO_CHAR(%1 ,%2)' AS CHAR "Inv"."Date Entered",'MM/DD/YYYY') ||'-'|| Evaluate('TO_CHAR(%1 ,%2)' AS CHAR ,"Inv"."Date Sent",'MM/DD/YYYY')

    Thank you!