How to compare month in obiee?

Question

How to write this case statement in obiee?

CASE WHEN to_char(to_date(hire_dt, 'MM-DD-YYYY'), 'Month') <> to_char(to_date(start_dt-1, 'MM-DD-YYYY'), 'Month') THEN 1 Else 0

Month not equal:

Example:

0   -> 09/14/2021   09/16/2021
1   -> 12/31/2019   03/15/2017

Answer 1

I just want to compare month regardless of the year.

In Oracle:

CASE
WHEN EXTRACT( MONTH FROM hire_dt ) = EXTRACT( MONTH FROM start_dt )
THEN 1
ELSE 0
END

or

CASE
WHEN TO_CHAR( hire_dt, 'MM' ) = TO_CHAR( start_dt, 'MM' )
THEN 1
ELSE 0
END

This OBIEE Date functions documentation suggests you can use MONTH:

CASE
WHEN MONTH( hire_dt ) = MONTH( start_dt )
THEN 1
ELSE 0
END