Discussions

changing date field appearance in OBIEE

Jacob Esson
Jacob Esson Posts: 30 Silver Medal

there are various ways to change the appearance of a Date field.

column Properties

column properties>tab date format>tick overide Default Date Format>select date format that is most applicable to your date requirements (you can change) for example (dd MMMM yyyy h:mm:ss tt) > select custom in the dateformat selector> custom Date format becomes editable> here you can remove the timestamp by leaving "dd MMMM yyyy" in the field

Column Formula

this formula changes the timestamp into a format where the timestamp is changed to mm-YYYY.


For the first validate if the month <10

CASE WHEN MONTH("Email Group Subscribe Attributes"."Email Group Subscribe Date/Time") < 10


if that is the case we concatenate (using the ||) the value '0' with the Month Number as 1 digit

THEN '0'||CAST(MONTH("Email Group Subscribe Attributes"."Email Group Subscribe Date/Time")AS VARCHAR(1))

for the month numbers from October to December as two digits

ELSE CAST(MONTH("Email Group Subscribe Attributes"."Email Group Subscribe Date/Time")AS VARCHAR(2)) END


we then concatenate (using the double ||) and the value '-' (in between single quotation marks with the year

||'-'||CAST(YEAR("Email Group Subscribe Attributes"."Email Group Subscribe Date/Time")AS CHAR)


Formula: CASE WHEN MONTH("Email Group Subscribe Attributes"."Email Group Subscribe Date/Time") < 10 THEN '0'||CAST(MONTH("Email Group Subscribe Attributes"."Email Group Subscribe Date/Time")AS VARCHAR(1)) ELSE CAST(MONTH("Email Group Subscribe Attributes"."Email Group Subscribe Date/Time")AS VARCHAR(2)) END||'-'||CAST(YEAR("Email Group Subscribe Attributes"."Email Group Subscribe Date/Time")AS CHAR)


if you would like to use this field on other subjectareas, please replace the "Email Group Subscribe Attributes"."Email Group Subscribe Date/Time" in the formula