Change in date formats

Discussion forum about all things SqlBase.
FRBhote
India
Posts: 2176
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Change in date formats

Post by FRBhote » 08 Dec 2016, 08:25

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
Site Admin
Site Admin
Croatia
Posts: 1406
Joined: 05 Mar 2017, 12:37
Location: Zagreb, Croatia

Re: Change in date formats

Post by Igor Ivanovic » 08 Dec 2016, 13:18

Hi,

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

FRBhote
India
Posts: 2176
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: Change in date formats

Post by FRBhote » 08 Dec 2016, 14:53

Unfortunately it returns a string.

Decided to use a decode in both statements.

RainerE
Germany
Posts: 2049
Joined: 27 Apr 2021, 12:40
Location: Germany

Re: Change in date formats

Post by RainerE » 20 Dec 2016, 17:07

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

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests