如何使用Oracle Sql从日期中获得正确的ISO周数

Question

我尝试使用以下代码提取今年的正确iso周数，例如2019年1月7日使用以下代码，但它仍然返回一个日历而非基于iso的周数，即第1周而非第2周。说实话有点为难。NLS_TERRITORY设置为英国，如果这有什么不同？

to_number(to_char(to_date(date_created,'DD/MM/YYYY'),'IW')) as WEEK_PRODUCED

提前感谢您提供的任何帮助。

Answer 1

这似乎是因为隐式日期转换——假设date_created实际上是DATE列，而不是字符串。如果您的NLS设置设置为显示具有2位数年份的日期，例如仍然默认的'DD-Mon-RR'格式，那么您将日期值转换为字符串并再次返回的操作将丢失世纪。作为演示，通过CTE:提供您的日期值

alter session set nls_date_format = 'DD-Mon-RR';
with your_table (date_created) as (
select date '2019-01-07' from dual
)
select
to_char(date_created) as nls_date,
to_char(date_created, 'YYYY-MM-DD') as iso_date,
to_char(to_date(to_char(date_created), 'DD/MM/YYYY'), 'YYYY-MM-DD') as conv_date,
to_number(to_char(to_date(date_created,'DD/MM/YYYY'),'IW')) as WEEK_PRODUCED
from your_table;
NLS_DATE  ISO_DATE   CONV_DATE  WEEK_PRODUCED
--------- ---------- ---------- -------------
07-Jan-19 2019-01-07 0019-01-07             1

请注意，使用NLS 2位年份模型转换为字符串，然后使用4位年份模型返回日期的conv_date已经失去了它的世纪；你似乎和文字一样：

select to_char(to_date('01/07/19', 'DD/MM/YYYY'), 'YYYY-MM-DD') from dual;
TO_CHAR(TO
----------
0019-07-01

因为19年就是19年；这里没有任何暗示或机制可以假设你指的是本世纪。(这就是RRRR格式模型的用途。)

所以，你得到的是19年1月7日的ISO周数，而不是2019年，而那一年是ISO周1。

如果你的课程有一个4位数的年份模型，那么它会起作用：

alter session set nls_date_format = 'DD/MM/YYYY';
with your_table (date_created) as (
select date '2019-01-07' from dual
)
select
to_char(date_created) as nls_date,
to_char(date_created, 'YYYY-MM-DD') as iso_date,
to_char(to_date(to_char(date_created), 'DD/MM/YYYY'), 'YYYY-MM-DD') as conv_date,
to_number(to_char(to_date(date_created,'DD/MM/YYYY'),'IW')) as WEEK_PRODUCED
from your_table;
NLS_DATE   ISO_DATE   CONV_DATE  WEEK_PRODUCED
---------- ---------- ---------- -------------
07/01/2019 2019-01-07 2019-01-07             2

隐式生成的字符串现在有一个4位数的年份，因此将其转换回原来的世纪。(当然，你仍然会对BCE日期有问题…)

但你根本不应该转换原始日期。只需：

to_number(to_char(date_created,'IW')) as WEEK_PRODUCED

与中相同，具有相同的CTE:

with your_table (date_created) as (
select date '2019-01-07' from dual
)
select
to_number(to_char(date_created,'IW')) as WEEK_PRODUCED
from your_table;
WEEK_PRODUCED
-------------
2

无论你的NLS设置是什么，它都会起作用，因为不再有任何隐含的转换。