问题描述
我设法将Oracle 10g中的日期21-Feb-12017(我知道这不是一个正确的日期)输入到日期列中.甲骨文接受了罚款.当我尝试在SQL Developer中重新选择它时，SQL Developer将其显示为NULL.但是，当我尝试通过java检索日期时，我又回到了插入时的值.想知道发生了什么，因为我还可以看到Oracle将不同的5位数字年份转换为4位数字年份.我输入了21019年2月21日，Oracle在存储时将年份转换为4581.我什至可以在SQL Developer中选择此值.
I managed to enter the date 21-Feb-12017 (I know it's not a correct date) in Oracle 10g into a date column. Oracle accepted the date fine. When I tried to select it back in SQL Developer, SQL Developer displayed it as NULL. But when I tried to retrieve the date thru java, I got the value back as how I inserted. Wondering what's going on because I could also see that Oracle converted a different 5 digit year into a 4 digit year. I entered 21-Feb-21019 and Oracle converted the year to 4581 while storing. I could even select this value in SQL developer.
我想知道是否可以读取原始日期，例如21019年2月21日更改为4581年2月21日，那么如何读取21019年2月21日而不是4581年2月21日.
I was wondering if it be possible to read the original date back e.g 21-Feb-21019 changed to 21-Feb-4581, so how to read 21-Feb-21019 instead of 21-Feb-4581.
推荐答案
Oracle使用DATE > 7个字节，其中前2个字节为:
Oracle stores DATEs in tables using 7 bytes where the first 2 bytes are:
- 世纪+ 100
- 世纪+ 100
- Century + 100
- Year of century + 100
因此(技术上)可以存储的最大日期是当这两个字节的值分别为 255 和 199 时，它们的年份为 15599(我忽略了理论上您可以在第二个字节中存储 255 ，因为这会打开一大堆单独的问题).
So the maximum date that can (technically) be stored is when those two bytes have the values 255 and 199 which would give the a year of 15599 (I'm ignoring that you could theoretically store 255 in the second byte as that opens up a whole heap of separate issues).
您可以使用 DBMS_STATS.CONVERT_RAW_VALUE 将原始值转换为日期.我们可以绕过创建日期的常规方法，直接生成将要存储的字节值.
You can convert a raw value to a date using the DBMS_STATS.CONVERT_RAW_VALUE which means we can bypass the normal methods of creating dates and directly generate the byte values which will be stored.
此函数是一个示例:
CREATE FUNCTION createDate(
year int,
month int,
day int,
hour int,
minute int,
second int
) RETURN DATE DETERMINISTIC
IS
hex CHAR(14);
d DATE;
BEGIN
hex := TO_CHAR( FLOOR( year / 100 ) + 100, 'fm0X' )
|| TO_CHAR( MOD( year, 100 ) + 100, 'fm0X' )
|| TO_CHAR( month, 'fm0X' )
|| TO_CHAR( day, 'fm0X' )
|| TO_CHAR( hour + 1, 'fm0X' )
|| TO_CHAR( minute + 1, 'fm0X' )
|| TO_CHAR( second + 1, 'fm0X' );
DBMS_OUTPUT.PUT_LINE( hex );
DBMS_STATS.CONVERT_RAW_VALUE( HEXTORAW( hex ), d );
RETURN d;
END;
/
然后，如果您有日期列，则可以插入通常不允许插入的值:
Then if you have a date column you can insert values you are not normally allowed to insert:
CREATE TABLE table_name ( date_column DATE );
INSERT INTO table_name ( date_column )
VALUES ( DATE '2019-12-31' + INTERVAL '1:02:03' HOUR TO SECOND );
INSERT INTO table_name ( date_column ) VALUES ( createDate( 15599, 12, 31, 1, 2, 3 ) );
INSERT INTO table_name ( date_column ) VALUES ( createDate( 12017, 2, 21, 0, 0, 0 ) );
当年份超过日期的正常范围时，
TO_CHAR 不起作用.要获取存储在表中的值，可以使用 DUMP 获取包含字节值的字符串，也可以使用 EXTRACT 获取单个组件.
TO_CHAR does not work when the year exceeds the normal bounds of a date. To get the values stored in the table you can use DUMP to get a string containing the byte values or you can use EXTRACT to get the individual components.
SELECT DUMP( date_column ),
TO_CHAR( date_column, 'YYYY-MM-DD' ) AS value,
TO_CHAR( EXTRACT( YEAR FROM date_column ), 'fm00000' )
|| '-' || TO_CHAR( EXTRACT( MONTH FROM date_column ), 'fm00' )
|| '-' || TO_CHAR( EXTRACT( DAY FROM date_column ), 'fm00' )
|| ' ' || TO_CHAR( EXTRACT( HOUR FROM CAST( date_column AS TIMESTAMP ) ), 'fm00' )
|| ':' || TO_CHAR( EXTRACT( MINUTE FROM CAST( date_column AS TIMESTAMP ) ), 'fm00' )
|| ':' || TO_CHAR( EXTRACT( SECOND FROM CAST( date_column AS TIMESTAMP ) ), 'fm00' )
AS full_value
FROM table_name;
输出:
DUMP(DATE_COLUMN) | VALUE | FULL_VALUE
:-------------------------------- | :--------- | :-------------------
Typ=12 Len=7: 120,119,12,31,2,3,4 | 2019-12-31 | 02019-12-31 01:02:03
Typ=12 Len=7: 255,199,12,31,2,3,4 | 0000-00-00 | 15599-12-31 01:02:03
Typ=12 Len=7: 220,117,2,21,1,1,1 | 0000-00-00 | 12017-02-21 00:00:00
db<>小提琴此处
这篇关于Oracle 10g在日期中接受5位数字的年份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！