Joshua Liu created TRAFODION-2106:
--------------------------------------
Summary: to_char() can't compare with one date
Key: TRAFODION-2106
URL: https://issues.apache.org/jira/browse/TRAFODION-2106
Project: Apache Trafodion
Issue Type: Bug
Components: sql-cmp
Affects Versions: any
Reporter: Joshua Liu
1. Create one table josh_test
CREATE TABLE TRAFODION.SEABASE.JOSH_TEST
(
APPDATE VARCHAR(8) CHARACTER SET ISO88591 COLLATE
DEFAULT DEFAULT NULL SERIALIZED
, NAME VARCHAR(10) CHARACTER SET ISO88591 COLLATE
DEFAULT DEFAULT NULL SERIALIZED
)
;
Here appdate is of varchar(8) type
2. Insert some values into the table
SQL>select * from josh_test;
APPDATE NAME
-------- ----------
20140101 josh
20140102 jason
20140821 James
20140303 Tom
20150209 Axia
20150812 Jerry
20161010 Hong
20160802 Alex
3. Try to test to_char:
SQL>select to_date(appdate, 'YYYYMMDD'), name from josh_test;
(EXPR) NAME
---------- ----------
2014-01-01 josh
2014-01-02 jason
2014-08-21 James
2014-03-03 Tom
2015-02-09 Axia
2015-08-12 Jerry
2016-10-10 Hong
2016-08-02 Alex
--- 8 row(s) selected.
Here we can see that to_char seems worked.
4. Try to run another query
select * from josh_test where to_date(appdate, 'YYYYMMDD') > date '2015-01-01';
but failed:
*** ERROR[4041] Type CHAR(8) cannot be compared with type DATE. [2016-07-12
10:20:12]
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
