to_date to_timestamp to_char cast的一些总结



FF [1..9]

Yes

Fractional seconds; no radix character is printed. Use the X format element to add the radix character. Use the numbers 1 to 9 after FF to specify the number of digits in the fractional second portion of the datetime value returned. If you do not specify a digit, then Oracle Database uses the precision specified for the datetime data type or the data type's default precision. Valid in timestamp and interval formats, but not inDATE formats.

Examples: 'HH:MI:SS.FF'

SELECT TO_CHAR(SYSTIMESTAMP, 'SS.FF3') from DUAL;

DATE

Valid date range from January 1, 4712 BC, to December 31, 9999 AD. The default format is determined explicitly by theNLS_DATE_FORMAT parameter or implicitly by theNLS_TERRITORY parameter. The size is fixed at 7 bytes. This data type contains the datetime fieldsYEAR,MONTH, DAY,HOUR,MINUTE, andSECOND. It does not have fractional seconds or a time zone.

to:date函数:TO_DATE converts char ofCHAR,VARCHAR2,NCHAR, orNVARCHAR2 data type to a value ofDATE data type

注意:to_date的varchar2等参数,要是date格式的,不能有fractional seconds(如FF.6等) or a time zone

比如:下面都是错误的

select to_date('03-APR-13 01.15.31.673000 PM','DD-MON-RR HH.MI.SS.FF AM') from dual;select to_date('03-APR-13 01.15.31 PM','DD-MON-RR HH.MI.SS.FF AM') from dual;select to_date('03-APR-13 01.15.31.673000 PM','DD-MON-RR HH.MI.SS AM') from dual;

 

而下面的是正确的

select to_date('03-APR-13 01.15.31 PM','DD-MON-RR HH.MI.SS AM') from dual;

也就是说to_date函数,不管是expr还是fmt都不能出现.FF这样的参数

to:timestamp:TO_TIMESTAMP convertschar ofCHAR,VARCHAR2,NCHAR, orNVARCHAR2 data type to a value ofTIMESTAMP data type.

可写为

select to_timestamp('03-APR-13 01.15.31.543 PM','DD-MON-RR HH.MI.SS.FF AM') from dual;select to_timestamp('03-APR-13 01.15.31 PM','DD-MON-RR HH.MI.SS.FF AM') from dual;

我想应该是'03-APR-13 01.15.31 PM'虽然没有.FF，但是可以作为timestamp看待,所以可以放入to_timestamp

而'03-APR-13 01.15.31.543不能作为to_date参数,因为'03-APR-13 01.15.31.543不可能当做date类型，他有FF，而date类型是没有fractional seconds的。所以放入to_date里会出现错误

那如何用.ff[1-9]指定特有的精度呢？

我想说一下TO_TIMESTAMP并不能指定精度,他的作用只是将字符装成timestamp类型，你在to_timestamp里面写.FF就可以了,加精度没有意义,我用to_char来实现指定精度

to_char函数：TO_CHAR (datetime) converts a datetime or interval value of DATE,TIMESTAMP,TIMESTAMPWITHTIMEZONE,TIMESTAMPWITH LOCAL TIME ZONE,INTERVALDAYTOSECOND, orINTERVALYEAR TO MONTH data type to a value ofVARCHAR2 data type in the format specified by the date formatfmt.

注意是将时间参数转成字符

比如:

select to_char(systimestamp,'DD/MON/RR HH:MI:SS:FF2 AM') from dual;

而

select to_char(sysdate,'DD/MON/RR HH:MI:SS.FF7 AM') from dual;

则错误。

那如何把字符串转成指定精度的timestamp呢？需要先将字符串转成timestamp类型，然后再将timestamp转成确定精度fmt  .FF的varchar2

如下:

SELECT   TO_CHAR(    TO_TIMESTAMP('03-APR-13 01.15.31.6754542  PM',               'DD-MON-RR HH.MI.SS.FF AM'),  'DD-MON-RR HH.MI.SS.FF4 AM')FROM DUAL;

 

如何将timestamp转场date类型呢？我用cast函数

CAST converts one built-in data type or collection-typed value into another built-in data type or collection-typed value.

那么什么能转换什么呢？

Table 5-1 Casting Built-In Data Types

 from BINARY_FLOAT, BINARY_DOUBLEfrom CHAR, VARCHAR2fromNUMBERfrom DATETIME / INTERVAL (Note 1)fromRAWfrom ROWID, UROWID (Note 2)from NCHAR, NVARCHAR2

to BINARY_FLOAT, BINARY_DOUBLE

X

X

X

--

--

--

X

to CHAR, VARCHAR2

X

X

X

X

X

X

--

to NUMBER

X

X

X

--

--

--

X

to DATE, TIMESTAMP, INTERVAL

--

X

--

X

--

--

--

to RAW

--

X

--

--

X

--

--

to ROWID, UROWID

--

X

--

--

--

X

--

to NCHAR, NVARCHAR2

X

--

X

X

X

X

X

Note 1: Datetime/interval includes DATE,TIMESTAMP,TIMESTAMPWITHTIMEZONE,INTERVALDAYTOSECOND, and INTERVAL YEAR TO MONTH.

Note 2: You cannot cast a UROWID to a ROWID if the UROWID contains the value of a ROWID of an index-organized table.

注意X是能转的： 比如from DATETIME / INTERVAL (Note 1)能转到to DATE, TIMESTAMP, INTERVAL

--不能转:              比如from ROWID, UROWID (Note 2)不能转到to DATE, TIMESTAMP, INTERVAL

于是我用

select cast(systimestamp as date) from dual;

来完成timestamp to date的转换

最后实现一个复杂些的,如何把一个字符转成date类型呢？

刚看到
from CHAR, VARCHAR2 to  DATE, TIMESTAMP, INTERVAL 是X

于是我写了

SELECT   cast(    '03-APR-13 01.15.31.6754542  PM' as date    )              FROM DUAL;

但是出现错误:date format picture ends before converting entire input string

之后改成

SELECT   cast(    '03-APR-13 01.15.31.6754542  PM' as timestamp    )              FROM DUAL;

则可以,也就是说就算from CHAR, VARCHAR2 to  DATE, TIMESTAMP, INTERVAL 是X，也只能是根据varchar2的具体情况,选定合适的日期类型,而不像cast timestamp as date，由于'03-APR-13 01.15.31.6754542 PM'不是date类型，所以会出现错误。

我又写了

SELECT   cast (cast(    '03-APR-13 01.15.31.6754542  PM' as timestamp    ) as date)              FROM DUAL;

来完成转换