Is there a way (without formatting) to display date and time value in dd/mm hh:mm format?

Manoj Dixit

Hi,

We are on OBI EE 11.1.1.7.0.

There is a datetime column that displays date and time values in dd/mm/yyyy hh24:mi:ss format. User wants the values to be displayed in dd/mm hh24:mi format.

First, I tried changing data format. However, when the results are exported to a csv file, then the values are exported in dd/mm/yyyy hh24:mi:ss format. Our QA team had raised this as an issue - what you see was not being exported. Therefore, the formatting is out of question for now.

I have tried this.

case when "Folder"."Column" is null then null else CONCAT(CAST(DAYOFMONTH("Folder"."Column") AS VARCHAR(2)), CONCAT(CONCAT('/', CONCAT(CAST(MONTH("Folder"."Column") AS VARCHAR(2)), ' ')), CONCAT(CONCAT(CAST(HOUR("Folder"."Column") AS VARCHAR(2)), ':'), CAST(MINUTE("Folder"."Column") AS VARCHAR(2))))) end

Now, my problem with exporting to csv is solved. But, values less than 10 are shown as 1, for example, instead of 01. This applies to a day, month, hour or minute. From an end user's perspetive it will be confusing especially in case of displaying minute values. For example, 05/04/2016 10:01:00 being displayed as 5/4 10:1. We have columns that store time differences in seconds but displayed in minute and seconds format (330 seconds displayed as 5.5, for example) with rounding off set to 0, 1 and 2 decimals depending on user requirements. Considering this, it's not very much clear whether or not that ":1" is a 10 or any value between 11 and 14 or the real 01.

Is there a workaround to display values of day, month, hour and minute with a zero prefixed when the values are between 1 and 9?

I would like to avoid using EVALUATE function also.

Thank you.

Regards,

Manoj.