文档首页 > > 工具指南> Migration Tool SQL语法迁移工具> Oracle(Beta)语法迁移> 系统函数> 日期函数

日期函数

分享
更新时间: 2019/08/09 GMT+08:00

本节介绍日期函数,包括:

TO_DATE

在Oracle中,TO_DATE函数用于将字符类型(CHAR、VARCHAR2、NCHAR、NVARCHAR2)转换为日期类型。

输入:包含三个参数的TO_DATE函数

create table AC_T_CLAIMTASKHISTORY
 (
   TASKID           VARCHAR2(30) not null,
   USERCODE         VARCHAR2(10) not null,
   REGISTNO         VARCHAR2(22) not null,
   CURRWORKNODE     NUMBER(5) default 0,
   POLICYNO         VARCHAR2(22) not null,
   CREATETIME       DATE,
   TASKENDTIME      DATE,
   HASTRANSFER      VARCHAR2(1)
 )
 partition by range (CREATETIME)
 (
   partition P0 values less than (TO_DATE(' 1900-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
     tablespace TAB_KHYX_ECIF,
   partition PART_2018_01 values less than (TO_DATE(' 2018-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', ' NLS_CALENDAR = GREGORIAN '))
     tablespace TAB_KHYX_ECIF,
   partition PART_2018_02 values less than (TO_DATE(' 2018-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR = GREGORIAN'))
     tablespace TAB_KHYX_ECIF,
   partition SYS_P28766 values less than (TO_DATE(' 2018-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'nls_calendar=gregorian   '))
     tablespace TAB_KHYX_ECIF,
   partition SYS_P29844 values less than (TO_DATE(' 2018-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'Nls_calendar=Gregorian'))
     tablespace TAB_KHYX_ECIF
 );

输出

create table AC_T_CLAIMTASKHISTORY
 (
   TASKID           VARCHAR2(30) not null,
   USERCODE         VARCHAR2(10) not null,
   REGISTNO         VARCHAR2(22) not null,
   CURRWORKNODE     NUMBER(5) default 0,
   POLICYNO         VARCHAR2(22) not null,
   CREATETIME       DATE,
   TASKENDTIME      DATE,
   HASTRANSFER      VARCHAR2(1)
 )
 partition by range (CREATETIME)
 (
   partition P0 values less than (TO_DATE(' 1900-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'/*, 'NLS_CALENDAR=GREGORIAN'*/))
     tablespace TAB_KHYX_ECIF,
   partition PART_2018_01 values less than (TO_DATE(' 2018-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'/*, ' NLS_CALENDAR = GREGORIAN '*/))
     tablespace TAB_KHYX_ECIF,
   partition PART_2018_02 values less than (TO_DATE(' 2018-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'/*, 'NLS_CALENDAR = GREGORIAN'*/))
     tablespace TAB_KHYX_ECIF,
   partition SYS_P28766 values less than (TO_DATE(' 2018-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'/*, 'nls_calendar=gregorian   '*/))
     tablespace TAB_KHYX_ECIF,
   partition SYS_P29844 values less than (TO_DATE(' 2018-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'/*, 'Nls_calendar=Gregorian'*/))
     tablespace TAB_KHYX_ECIF
 ); 
说明:

在TO_DATE函数的第三个参数中,如果存在非NLS_CALENDAR=GREGORIAN的值,Migration Tool将会在日志中记录一个错误消息。

TIMESTAMP

在Oracle中,TIMESTAMP函数中包含DATE和TIME类型的值,返回值可以精确到小数点后9位。

输入:不同精确度的TIMESTAMP函数

CREATE TABLE mc_t_custimportmessage
 (
   importid             VARCHAR2(30) not null,
   status               VARCHAR2(1),
   oldcustid            VARCHAR2(30),
   oldcarid             VARCHAR2(30),
   carstatus            VARCHAR2(2),
   customercname        VARCHAR2(200),
   identifytype         VARCHAR2(30),
   identifynumber       VARCHAR2(60),
   phonenumber1         VARCHAR2(50),
   policyno             VARCHAR2(66),
   lastciinsuredcompany VARCHAR2(50),
   lastbiinsuredcompany VARCHAR2(50),
   inserttimeforhis     TIMESTAMP(9) default sysdate,
   operatetimeforhis    TIMESTAMP(9),
   monopolyflag         VARCHAR2(3)
 );

输出

CREATE TABLE mc_t_custimportmessage
 (
   importid             VARCHAR2(30) not null,
   status               VARCHAR2(1),
   oldcustid            VARCHAR2(30),
   oldcarid             VARCHAR2(30),
   carstatus            VARCHAR2(2),
   customercname        VARCHAR2(200),
   identifytype         VARCHAR2(30),
   identifynumber       VARCHAR2(60),
   phonenumber1         VARCHAR2(50),
   policyno             VARCHAR2(66),
   lastciinsuredcompany VARCHAR2(50),
   lastbiinsuredcompany VARCHAR2(50),
   inserttimeforhis     TIMESTAMP(6) default sysdate,
   operatetimeforhis    TIMESTAMP(6),
   monopolyflag         VARCHAR2(3)
 );
分享:

    相关文档

    相关产品

文档是否有解决您的问题?

提交成功!

非常感谢您的反馈,我们会继续努力做到更好!

反馈提交失败,请稍后再试!

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问,您也可以通过华为云社区问答频道来与我们联系探讨

跳转到云社区