Change in date formats

[FRBhote]

When I use a decode with date on the ISLAND db, the date is in yyyy-MM-dd:

SELECT @DECODE( INVOICE_DATE, '01-JAN-1990', '01-DEC-2030', INVOICE_DATE ) FROM INVOICE WHERE COMPANY_ID = 120;

@DECODE( INVOICE_DATE, '01-JAN-1990', '01-DEC-2030', INVOICE_DATE )
====================================================================
1995-10-16
1995-10-16

And without the decode it is dd-MMM-yyyy:

SELECT INVOICE_DATE FROM INVOICE WHERE COMPANY_ID = 120;

INVOICE_DATE
============
16-OCT-1995
16-OCT-1995

Which causes a problem in a UNION:

SELECT @DECODE( INVOICE_DATE, '01-JAN-1990', '01-DEC-2030', INVOICE_DATE ) FROM INVOICE WHERE COMPANY_ID = 120
UNION
SELECT INVOICE_DATE FROM INVOICE WHERE COMPANY_ID = 120;

SELECT @DECODE( INVOICE_DATE, '01-JAN-1990', '01-DEC-2030', INVOICE_DATE ) FROM INVOICE WHERE COMPANY_ID = 120
UNION
SELECT INVOICE_DATE FROM INVOICE WHERE COMPANY_ID = 120
^
Error: 01007 EDT MTE Mismatch in types of SELECT expressions

Reason: Attempting a SQL SELECT statement with a UNION and corresponding
columns of the two SELECTs have different data types, different
lengths (if not numeric), or the NOT NULL attributes of two
fields are not consistent.

Remedy: Correct the SQL SELECT statement so that each column of the
SELECT statements have the same data type with the same lengths
and so that each column defined as NOT NULL in one SELECT
statement corresponds to another NOT NULL column in the other
SELECT statement.

How can I get both the date formats same so that I can use a UNION?

[Igor Ivanovic]

Hi,

You can use @DATETOCHAR for both @decode(...) and invoice_date.

[FRBhote]

Unfortunately it returns a string.

Decided to use a decode in both statements.

[RainerE]

Try:

SELECT @datevalue( @DECODE( INVOICE_DATE, 1990-01-01, 2030-12-01, INVOICE_DATE ) ) FROM INVOICE WHERE COMPANY_ID = 120
UNION
SELECT INVOICE_DATE FROM INVOICE WHERE COMPANY_ID = 120;

Regards,
Rainer