如何在Oracle中获得第一天和最后一天?

I need to get the first day and the last day of the week from some strings that have the format like this:

我需要从一些具有如下格式的字符串中获得第一天和最后一天:

'201118'

“201118”

where 2011 is the year and 18 is the number of the week. Knowing the number of the week, how do I get the first and the last day of the week?

2011年是一年，18是一周。知道了一周的星期数，我如何得到一周的第一天和最后一天?

How do I do this?

我该怎么做呢?

11 个解决方案

#1

WEEK

周

select TRUNC(sysdate, 'iw') AS iso_week_start_date,
       TRUNC(sysdate, 'iw') + 7 - 1/86400 AS iso_week_end_date
from dual;

MONTH

月

select 
TRUNC (sysdate, 'mm') AS month_start_date,
LAST_DAY (TRUNC (sysdate, 'mm')) + 1 - 1/86400 AS month_end_date
from dual;

#2

If you are using Oracle, this code can help you:

如果您正在使用Oracle，此代码可以帮助您:

select 
TRUNC(sysdate, 'YEAR') Start_of_the_year,
TRUNC(sysdate, 'MONTH') Start_of_the_month,
TRUNC(sysdate, 'DAY') start_of_the_week,
TRUNC(sysdate+365, 'YEAR')-1 End_of_the_year,
TRUNC(sysdate+30, 'MONTH')-1 End_of_the_month,
TRUNC(sysdate+6, 'DAY')-1 end_of_the_week
from dual;


select 
TRUNC(sysdate, 'YEAR') Start_of_the_year,
TRUNC(sysdate+365, 'YEAR')-1 End_of_the_year,
TRUNC(sysdate, 'MONTH') Start_of_the_month,
TRUNC(sysdate+30, 'MONTH')-1 End_of_the_month,
TRUNC(sysdate, 'DAY')+1 start_of_the_week,  -- starting Monday
TRUNC(sysdate+6, 'DAY') end_of_the_week     -- finish Sunday
from dual;

#3

SQL> var P_YEARWEEK varchar2(6)
SQL> exec :P_YEARWEEK := '201118'

PL/SQL procedure successfully completed.

SQL> with t as
  2  ( select substr(:P_YEARWEEK,1,4) year
  3         , substr(:P_YEARWEEK,5,2) week
  4      from dual
  5  )
  6  select year
  7       , week
  8       , trunc(to_date(year,'yyyy'),'yyyy')                              january_1
  9       , trunc(trunc(to_date(year,'yyyy'),'yyyy'),'iw')                  monday_week_1
 10       , trunc(trunc(to_date(year,'yyyy'),'yyyy'),'iw') + (week - 1) * 7 start_of_the_week
 11       , trunc(trunc(to_date(year,'yyyy'),'yyyy'),'iw') + week  * 7 - 1  end_of_the_week
 12    from t
 13  /

YEAR WE JANUARY_1           MONDAY_WEEK_1       START_OF_THE_WEEK   END_OF_THE_WEEK
---- -- ------------------- ------------------- ------------------- -------------------
2011 18 01-01-2011 00:00:00 27-12-2010 00:00:00 25-04-2011 00:00:00 01-05-2011 00:00:00

1 row selected.

Regards,
Rob.

问候,抢劫。

#4

SELECT NEXT_DAY (TO_DATE ('01/01/'||SUBSTR('201118',1,4),'MM/DD/YYYY')+(TO_NUMBER(SUBSTR('201118',5,2))*7)-3,'SUNDAY')-7 first_day_wk,
    NEXT_DAY (TO_DATE ('01/01/'||SUBSTR('201118',1,4),'MM/DD/YYYY')+(TO_NUMBER(SUBSTR('201118',5,2))*7)-3,'SATURDAY') last_day_wk FROM dual

#5

Assuming you mean weeks relative to the first day of the year ...

假设你的平均时间是一年的第一天……

SELECT first_day_of_week, first_day_of_week+6 last_day_of_week
FROM (
  SELECT TO_DATE(YEAR||'0101','YYYYMMDD') + 7 * (week-1) first_day_of_week
  FROM (
    SELECT substr(yearweek,1,4) YEAR, to_number(substr(yearweek,5)) week
    FROM (
      SELECT '201118' yearweek FROM dual
    )
  )
)
;

#6

You could try this approach:

你可以试试这个方法:

1. Get the current date.

   获得当前日期。

2. Add the the number of years which is equal to the difference between the current date's year and the specified year.

   增加年数，该年数等于当前日期的年份与指定年份之间的差额。

3. Subtract the number of days that is the last obtained date's week day's number (and it will give us the last day of some week).

   减去最后获得的日期的星期数(它将给出某个星期的最后一天)。

4. Add the number of weeks which is the difference between the last obtained date's week number and the specified week number, and that will yield the last day of the desired week.

   加上周数，即最后获得的日期的周数和指定的周数之间的差，这将产生所需周的最后一天。

5. Subtract 6 days to obtain the first day.

   减去6天获得第一天。

#7

Unless this is a one-off data conversion, chances are you will benefit from using a calendar table.

除非这是一次性的数据转换，否则您很可能会受益于使用日历表。

Having such a table makes it really easy to filter or aggregate data for non-standard periods in addition to regular ISO weeks. Weeks usually behave a bit differently across companies and the departments within them. As soon as you leave "ISO-land" the built-in date functions can't help you.

除了常规的ISO周之外，拥有这样一个表可以很容易地过滤或聚合非标准周期的数据。周通常在不同的公司和不同的部门有不同的表现。一旦您离开“ISO-land”，内置的日期函数就不能帮助您。

create table calender(
   day           date      not null -- Truncated date
  ,iso_year_week number(6) not null -- ISO Year week  (IYYYIW)
  ,retail_week   number(6) not null -- Monday to Sunday (YYYYWW)
  ,purchase_week number(6) not null -- Sunday to Saturday (YYYYWW)
  ,primary key(day)
);

You can either create additional tables for "purchase_weeks" or "retail_weeks", or simply aggregate on the fly:

您可以创建额外的表来“购买”或“retail_weeks”，或者简单地聚合在一起:

select a.colA
      ,a.colB
      ,b.first_day
      ,b.last_day
  from your_table_with_weeks a
  join (select iso_year_week
              ,min(day) as first_day
              ,max(day) as last_day
          from calendar
         group  
            by iso_year_week
       ) b on(a.iso_year_week = b.iso_year_week)

If you process a large number of records, aggregating on the fly won't make a noticable difference, but if you are performing single-row you would benefit from creating tables for the weeks as well.

如果您处理大量的记录，那么动态聚合不会产生明显的差异，但是如果您执行单行，那么您也可以在几周内创建表。

Using calendar tables provides a subtle performance benefit in that the optimizer can provide better estimates on static columns than on nested add_months(to_date(to_char())) function calls.

使用calendar表提供了一个微妙的性能好处，优化器可以提供比嵌套add_months(to_date(to_char())函数调用更好的静态列估计。

#8

Actually, I did something like this:

事实上，我做过这样的事情:

select case myconfigtable.valpar
   when 'WEEK' then to_char(next_day(datetime-7,'Monday'),'DD/MM/YYYY')|| ' - '|| to_char(next_day(datetime,'Sunday'),'DD/MM/YYYY')
   when 'MONTH' then to_char(to_date(yearweek,'yyyyMM'),'DD/MM/YYYY')  || ' - '|| to_char(last_day(to_date(yearweek,'yyyyMM')),'DD/MM/YYYY')
   else 'NA'
   end

from ( select to_date(YEAR||'01','YYYYMM') + 7 * (WEEK - 1) datetime, yearweek from ( select substr(yearweek,1,4) YEAR, to_number(substr(yearweek,5)) WEEK, yearweek from (select '201018' yearweek from dual ) ) ), myconfigtable myconfigtable where myconfigtable.codpar='TYPEOFPERIOD'

从(选择to_mydate (YEAR||'01'，'YYYYMM') + 7 * (WEEK - 1) datetime, yearweek from (select substr(YEAR WEEK, 1,4) YEAR, to_number(sub - str, YEAR - WEEK, 5))， yearweek from (select '201018' YEAR - YEAR - YEAR - YEAR - YEAR - YEAR - YEAR - YEAR - YEAR - YEAR - from configmypar)，

#9

Yet another solution (Monday is the first day):

另一个解决方案(周一是第一天):

select 
    to_char(sysdate - to_char(sysdate, 'd') + 2, 'yyyymmdd') first_day_of_week
    , to_char(sysdate - to_char(sysdate, 'd') + 8, 'yyyymmdd') last_day_of_week
from
    dual

#10

I think this is just a simple select statement. I hope it works, because I couldn't test it at home, because I don't have a Oracle database here ;-)

我认为这只是一个简单的选择语句。我希望它能工作，因为我不能在家里测试它，因为我这里没有Oracle数据库;

select to_date('201118', 'YYYYWW'), to_date('201118', 'YYYYWW')+7 from dual;

You have to be carefully because there is a difference between WW and IW. Here is an article which explains the difference: http://rwijk.blogspot.com/2008/01/date-format-element-ww.html

你必须小心，因为WW和IW是不同的。这里有一篇文章解释了这种差异:http://rwijk.blogspot.com/2008/01/date-format-element-ww.html

#11

First day of week (Monday):

每周第一天(星期一):

SELECT TO_DATE(to_char(sysdate,'YYYY')||'0101','YYYYMMDD') + 7 * to_number(to_char(sysdate,'WW')-1)-1 first_day_week FROM dual;

Last day of week (Sunday):

最后一天(星期日):

SELECT TO_DATE(to_char(sysdate,'YYYY')||'0101','YYYYMMDD') + 7 * to_number(to_char(sysdate,'WW')-1)+5 last_day_week FROM dual;

Substituting your date or date field in these formulas will work for you!

在这些公式中替换您的日期或日期字段将对您有效!