SQL Oracle SQL中'YYYY'和'RRRR'有什么区别
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6812916/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the difference between 'YYYY' and 'RRRR' in Oracle SQL
提问by Nitish
I have 2 queries in SQL:
我在 SQL 中有 2 个查询：
select trunc(to_date('27-Jul-1987'),'YYYY') FROM dual;
and
和
select trunc(to_date('27-Jul-1987'),'RRRR') FROM dual;
Both are giving me the same result. What is the difference between 'RRRR' and 'YYYY'?
两者都给我相同的结果。“RRRR”和“YYYY”有什么区别？
回答by Bohemian
YYYYgives the current year as 4 digits.
YYYY给出当前年份为 4 位数字。
RRRRformat means 2-digit years in the range 00to 49are assumed to be in the currentcentury (ie have the same first two digits as the current year), and years given as 50through 99are assumed to be in the previouscentury.
RRRR格式装置2位数年的范围内00，以49被认为是在当前世纪（即具有相同的前两个数字作为当前年），并给出年50通过99被认为是在前面的世纪。
回答by Pranav Shah
If the first 2 digits for the year are not specified in the date to be converted:
如果要转换的日期中未指定年份的前 2 位数字：
- YYYY will always return the current year.
- RRRR returns the year based on the current specified year in the database.
- YYYY 将始终返回当前年份。
- RRRR 根据数据库中当前指定的年份返回年份。
Try this sample code:
试试这个示例代码：
SELECT TO_DATE ('010199', 'MMDDYYYY') AS date_a,
TO_DATE ('010199', 'MMDDYY') AS date_b,
TO_DATE ('010199', 'MMDDRR') AS date_c,
TO_DATE ('010199', 'MMDDRRRR') AS date_d
FROM DUAL;
The results when run on 12/01/2014:
2014 年 1 月 12 日运行的结果：
DATE_A DATE_B DATE_C DATE_D
--------- --------- --------- ---------
1/1/0099 1/1/2099 1/1/1999 1/1/1999
This oracle linkgives a great description and examples.
这个 oracle链接提供了很好的描述和示例。
From the above link:
从上面的链接：
- If the specified two-digit year is 00 to 49, then
- If the last two digits of the current year are 00 to 49, then the returned year has the same first two digits as the current year.
- If the last two digits of the current year are 50 to 99, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.
- If the specified two-digit year is 50 to 99, then
- If the last two digits of the current year are 00 to 49, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.
- If the last two digits of the current year are 50 to 99, then the returned year has the same first two digits as the current year.
- 如果指定的两位数年份是 00 到 49，则
- 如果当前年份的最后两位数字是 00 到 49，则返回的年份的前两位数字与当前年份相同。
- 如果当前年份的后两位数字是 50 到 99，则返回年份的前 2 位数字比当前年份的前 2 位数字大 1。
- 如果指定的两位数年份是 50 到 99，则
- 如果当前年份的后两位数字是 00 到 49，则返回年份的前 2 位数字比当前年份的前 2 位数字小 1。
- 如果当前年份的最后两位数字是 50 到 99，则返回的年份的前两位数字与当前年份相同。
回答by gfytd
This:
这个：
SQL> select to_char(to_date('72-01-01','rrrr-mm-dd'),'yyyy') from dual;
will give you:
会给你：
1972
1972年
But this:
但是这个：
SQL> select to_char(to_date('72-01-01','yyyy-mm-dd'),'yyyy') from dual;
will give you:
会给你：
0072
