# General OBIEE

## Get Involved. Join the Conversation.

This is a public Forum  public

## Topic

get greatest of two dates in OTBI
Topic posted January 31, 2019 by Parthiban Vaithiyanathan, tagged Analyses, OBIEE Answers, OTBI
Title:
get greatest of two dates in OTBI
Content:

Hi,

I need to find greatest /least of two dates in OTBI using formula

how can we do this in OTBI?

## Comment

• Hi - assuming that the two dates are the same field, then you would do MAX("FIELD").

• Hi - dates are in two different field

• OK, try this then:

CASE
WHEN "FIELD1" > "FIELD2" THEN "FIELD1"
WHEN "FIELD2" > "FIELD1" THEN "FIELD2"
END

• Wait where's the ELSE? What if they're the same? :-P

• You do not always need the ELSE on there, in this statement it would just return a blank cell.

If you want it to return something for it being the same, you would do something like this:

CASE
WHEN "FIELD1" > "FIELD2" THEN "FIELD1"
WHEN "FIELD2" > "FIELD1" THEN "FIELD2"
ELSE 'SAME'
END

When I tested this, it errored because FIELD1 and FIELD2 are dates ... so I had to use code like this:

CASE
WHEN "FIELD1" > "FIELD2" THEN 'Field 1 name'
WHEN "FIELD2" > "FIELD1" THEN 'Field 2 name'
ELSE 'SAME'
END

I am not sure how to combine the text/date fields for it to work.

• Are the fields dates or timestamps? Either way, you should be able to use TIMESTAMPDIFF and some CASE statement. Pseudocode below:

```TIMESTAMPDIFF(SQL_TSI_DAY, TIMESTAMPADD(SQL_TSI_DAY, -7, CURRENT_DATE),TIMESTAMPADD(SQL_TSI_DAY, 0, CURRENT_DATE))
```

...

CASE when (above formula >= 0) then date on right of method ELSE date on left of method END

Hope this helps.