SQL Oracle从多个DateTime行获取周数

i有70.000行数据，包括日期时间列(yyyy-mm-dd hh24-mm-ss。(。我想将这些数据分为3个单独的列；小时，每天和周数。

日期时间列名是从表'合同'中的'registrationdate'。

这是我到目前为止的日常列：

SELECT substr(REGISTRATIONDATE, 0, 10) AS "Date",
substr(REGISTRATIONDATE, 11, 9) AS "Hour"
FROM CONTRACTS;

我已经看到了获得特定日期的一周号的选项，此任务涉及70.000日期，因此这不是一个选项。

您(OP(仍然必须解释一年中的头几天要分配的星期数，直到一年中的第一个星期一。您是否为上一年的日历年分配了一个星期的电话号码？我在评论中询问了2017年1月1日，例如；那是一个星期日。根据您的定义，2017年1月2日至2017年1月8日的一周是"第1周"；您分配了哪个星期的编号到2017年1月1日(星期日(？

下面的直接计算分配给IT周编号 0 。除此之外，计算是微不足道的。

注意：要在任何给定的日期dt找到本周的星期一，我们可以使用trunc(dt, 'iw')。 IW 代表ISO周，标准周，从星期一开始，在周日结束。

然后：要找到一年的第一个星期一，我们可以从1月7日的日期开始，并要求1月7日落在一周的星期一。(我不会解释一个 - 这很容易逻辑，并且与编程无关。(

要输入固定日期，最好的方法是使用日期文字语法：1月7日的date '2017-01-07'。

so：要找到任何日期dt的周号，请计算

1 + ( trunc(dt, 'iw') - trunc(date '2017-01-07', 'iw') ) / 7

此公式找到了dt的ISO周的星期一，并减去了今年的第一个星期一 - 使用Oracle Date Arithmetic，其中两个日期之间的差异是它们之间的天数。因此，要找到几周数除以7；要将第一个星期一分配给数字1，而不是0，我们需要将1添加到7.

的结果中。

您要解决的另一个问题是将字符串转换为日期。最好的解决方案是修复数据模型本身(更改列的数据类型，以便它是DATE而不是VARCHAR2(；那么您需要更轻松地提取所需的所有数据，您将确保您的数据中没有诸如'2017-02-29 12:30:00'之类的日期(目前，如果您这样做，您将拥有很难使任何日期计算起作用(，查询将要快很多。

如果格式为'mm/dd/yyyy'

，假设您的注册说明

simples(和更快的(查询基于to_char(to_date(registrationdate，'mm/dd/yyyy'(，'ww'((否则，请在适当的日期中转换您的列，然后将对话进行至周的编号(

SELECT substr(REGISTRATIONDATE, 0, 10) AS "Date",
substr(REGISTRATIONDATE, 11, 9) AS "Hour",
to_char(to_date(REGISTRATIONDATE,'MM/DD/YYYY'),'WW') as "Week"
FROM CONTRACTS;

这很混乱，但是看起来有效：

to_char(
        to_date(RegistrationDate,'YYYY-MM-DD HH24-MI-SS') + 
        to_number(to_char(trunc(to_date(RegistrationDate,'YYYY-MM-DD HH24-MI-SS'),'YEAR'),'D'))
        - 2,
        'WW')

在外部，您拥有其他人以前给出的解决方案，但使用了正确的日期格式。在中间，有一定天数的调整，以调整1月1日跌倒的位置。trunc零件从日期开始获得JAN的第一个，'D'获得了1月1日的工作日。由于1代表周日，我们必须使用-2来获得我们的需求。

edit ：我稍后可能会删除此答案，但是在我看来，来自@mathguy的一个是最好的。另请参阅有关该答案的评论，以了解如何扩展到一般解决方案。

，但首先您需要：

决定在第一个星期一之前在1月做什么，
解决日期中的潜在问题，以阻止其转换为日期。

在第1点，如果分配第0周是不可接受的(您想要第52/53周(，它会变得更加复杂，但是我们仍然可以提供帮助。

我看到的是，在第2点，要么有系统地错误(也许是时间戳，包括一秒钟的分数(，要么是无效的数据。

长度或格式或特定值不计算。您收到的错误消息表明，至少某些数据"太长"，我的评论中的代码应帮助您找到。

相关内容

最新更新

热门标签：