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 in Examples:
|
| Valid date range from January 1, 4712 BC, to December 31, 9999 AD. The default format is determined explicitly by the |
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
,TIMESTAMP
WITH
TIME
ZONE
,TIMESTAMP
WITH
LOCAL
TIME
ZONE
,INTERVAL
DAY
TO
SECOND
, orINTERVAL
YEAR
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_DOUBLE | from CHAR, VARCHAR2 | fromNUMBER | from DATETIME / INTERVAL (Note 1) | fromRAW | from ROWID, UROWID (Note 2) | from NCHAR, NVARCHAR2 | |
---|---|---|---|---|---|---|---|
to BINARY_FLOAT, BINARY_DOUBLE |
|
|
|
|
|
|
|
to CHAR, VARCHAR2 |
|
|
|
|
|
|
|
to NUMBER |
|
|
|
|
|
|
|
to DATE, TIMESTAMP, INTERVAL |
|
|
|
|
|
|
|
to RAW |
|
|
|
|
|
|
|
to ROWID, UROWID |
|
|
|
|
|
|
|
to NCHAR, NVARCHAR2 |
|
|
|
|
|
|
|
Note 1: Datetime/interval includes DATE
,TIMESTAMP
,TIMESTAMP
WITH
TIMEZONE
,INTERVAL
DAY
TO
SECOND
, 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;
来完成转换