Trafodion虽然符合ANSI99标准,但Build-In函数并非完全与Oracle或其他关系型数据库完全一致。拿常用的日期与字符串转换问题来说,Trafodion虽然也有to_char或to_date这样的函数,但与Oracle里面对应的函数还是有一些区别,不过Trafodion提供了一个通用的类型转换函数CAST。下面举几个常用的关于日期与字符串相互转换的例子供参考,
1 Timestamp转为Date
SQL>select cast(current_timestamp as date) from dual;
(EXPR)
----------
2016-11-24
2 Timestamp转为yyyy-mm-dd hh24:mi:ss字符串
SQL>select cast(current_timestamp as varchar(19)) from dual;
(EXPR)
------------------------------
2016-11-24 17:08:57
SQL>select to_char(current_timestamp,'YYYY-MM-DD HH24:MI:SS') from dual;
(EXPR)
-------------------
2017-07-28 08:48:54
3 Timestamp转为yyyy-mm-dd字符串
SQL>select substring(cast(current_timestamp as varchar(30)) from 1 for 10) from dual;
(EXPR)
----------
2016-11-24
SQL>select to_char(current_timestamp,'YYYY-MM-DD') from dual;
(EXPR)
----------
2017-07-28
4 Date转为yyyy-mm-dd字符串
SQL>select cast(current_date as varchar(10)) from dual;
(EXPR)
----------
2016-11-24
SQL>select to_char(current_date,'YYYY-MM-DD') from dual;
(EXPR)
----------
2017-07-28
5 字符串转为Date
SQL>select date'2016-10-10' from dual;
(EXPR)
----------
2016-10-10
SQL>select cast('2016-10-10' as date) from dual;
(EXPR)
----------
2016-10-10
SQL>select to_date('2016-10-10') from dual;
(EXPR)
----------
2016-10-10
>>select to_date('20170604042500','YYYYMMDDHH24MISS') from dual;
(EXPR)
--------------------------
2017-06-04 04:25:00.000000
6 字符串转为Timestamp
SQL>select timestamp'2016-10-11 23:30:59' from dual;
(EXPR)
-------------------
2016-10-11 23:30:59
SQL>select cast('2016-10-11 23:30:59' as timestamp) from dual;
(EXPR)
--------------------------
2016-10-11 23:30:59.000000
SQL>select to_timestamp('2016-10-11 23:30:59') from dual;
(EXPR)
--------------------------
2016-10-11 23:30:59.000000
SQL>select cast('1947-11-28' as timestamp) from dual;
(EXPR)
--------------------------
1947-11-28 00:00:00.000000
SQL>select to_timestamp('1947-11-28') from dual;
(EXPR)
--------------------------
1947-11-28 00:00:00.000000
关于Trafodion中to_date与to_char的详细用法,请参考Trafodion官网文档: http://trafodion.incubator.apache.org/docs/sql_reference/index.html#to_date_function及http://trafodion.incubator.apache.org/docs/sql_reference/index.html#to_char_function