Trafodion 日期与字符串类型转换

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