Oracle date weekly explanation and weekly start and end time calculation, oracle End Time

Oracle date weekly explanation and weekly start and end time calculation, oracle End Time

1 ORACLE weekly knowledge description

1.1 date formatting Functions

TO_CHAR (X [, FORMAT]): Convert X to a string in FORMAT. X is a date, and FORMAT is a string that specifies the FORMAT used for conversion. The FORMAT is related to the week W, WW, IW, D, FMWW.

WThe week of a month. Returns the number of weeks according to the standard week customized by ORACLE.

IWIt is an ISO standard week. It indicates that the ISO standard week is based on the week type as the "Main Line". A maximum of 53 weeks are allowed each year, but at least 52 weeks are required each year; if there are more than or equal to four days between 52nd weeks in a year and December 31 days in that year, it is set to 53rd weeks in that year. Otherwise, the remaining days are classified as 1st weeks in the next year; if it is less than 52 weeks, it will be supplemented in the following year. The weekly period is fixed from Monday to 1st days of the week, and from Sunday to 7th days of the week. For example: in Oracle, January 01, 2012 still belongs to the 2011 day of week 52nd of week IW, 7th. This is usually used.

WWIt is a standard week customized by ORACLE. It indicates the first day of the first week of the year (no matter the day of the week in the year). For example: january 1, 2014-01 is Wednesday. It is defined in Oracle as the first day of the first week of WW on January 1, 2014. It is rarely used.

DYes. The current date is the day of the week. It is calculated from Sunday to Saturday, which is important to note.

FMWWThe day of the year starting from January 1, January 1 (excluding the day of the week) to the day of the year is the first week, and the second week is counted from the first week of the year (which is the same as the IW algorithm) the last week of the year ends on January 1, December 31.

1.2 Date and Time Calculation Functions

NEXT_DAY (X, Y): Used to calculate the time of Y in the first week after X. Y is a string that represents the full name of a day in a week (such as Monday and Tuesday) in the current session language. It can also be a value.

TRUNC (X [, FORMAT]): Truncation date. The week-related values in the FORMAT are D, IW, WW, W, and FMWW.

WThe week of a month. A week is returned according to the standard week customized by ORACLE.

IWIt is the ISO standard week and returns the Monday of the week of the current date.

WWIt is the standard week customized by ORACLE. Returns the Monday of the ORACLE custom standard Week.

DReturns the first day of the current week. It is strange that D returns Sunday on the first day of the current week. This is what we should pay attention.

FMWWThe day of the year starting from January 1, January 1 (excluding the day of the week) to the day of the year is the first week, and the second week is counted from the first week of the year (which is the same as the IW algorithm) the last week of the year ends on January 1, December 31. The start time of the week is different from that of the IW in the Cross-year period. For example, when FMWW is used from January 1, to 01, the start time of the week is 2012. When IW is used, the start time of the week is.

ROUND (X [, FORMAT]): DAY in the week-related FORMAT rounded to the date. From Monday to Wednesday to Sunday.

2. Obtain the start time and end time of a week based on the given time.

-- Select trunc (TO_DATE ('1970-07-18 ', 'yyyy-MM-DD'), 'iw') as startdate from dual; -- this week Monday select trunc (TO_DATE ('1970-07-18 ', 'yyyy-MM-DD'), 'iw') + 6 as enddate from dual; -- select trunc (TO_DATE ('1970-07-18 ', 'yyyy-MM-DD'), 'iw')-7 as startdate from dual; --- last week Monday select trunc (TO_DATE ('1970-07-18 ', 'yyyy-MM-DD'), 'iw')-1 as enddate from dual; -- last week Sunday

3. Obtain the start time and end time of a week based on the number of weeks.

Depending on the start time and end time of a natural week, it is necessary to determine whether the days at the beginning of the year belong to the first week of the year or the last week of the year. According to the definition of the IW natural week, if less than or equal to 3 days is the current year, it belongs to the last week of the previous year, and if it is more than or equal to 4 days belongs to the current year, plus the last days of the previous year, it is counted as the first week of the current year.

-- Calculate the start time and end time of a week based on Monday to Sunday (IW) for a natural week with params as (select trunc (TO_DATE ('2017-01-01 ', 'yyyy-MM-DD '), 'yyyy') as sd from dual) select level weekly, DECODE (SIGN (5-DECODE (TO_CHAR (PM. SD, 'D'), '1', '7', TO_CHAR (PM. SD, 'D'),-1, NEXT_DAY (PM. SD + (LEVEL-1) * 7,2), NEXT_DAY (PM. SD + (LEVEL-1) * 7-7, 2) on the first day of the week, DECODE (SIGN (5-DECODE (TO_CHAR (PM. SD, 'D'), '1', '7', TO_CHAR (PM. SD, 'D'),-1, NEXT_DAY (PM. SD + (LEVEL-1) * 7,2), NEXT_DAY (PM. SD + (LEVEL-1) * 7-7, 2 )) + 6 from dual dleft join params pm on 1 = 1 connect by level <= 53 -- calculate the start time and end time of a week FROM Sunday to Saturday (D) select level weekly, (TRUNC (TO_DATE ('1970-01-01 ', 'yyyy-MM-DD'), 'yyyy')-7) + (7-TO_CHAR (TRUNC (TO_DATE ('1970-01-01 ', 'yyyy-MM-DD'), 'yyyy'), 'D') + 1) + (LEVEL-1) * 7 on the first day of the week, (TRUNC (TO_DATE ('1970-01-01 ', 'yyyy-MM-DD'), 'yyyy')-7) + (7-TO_CHAR (TRUNC (TO_DATE ('1970-01-01 ', 'yyyy-MM-DD'), 'yyyy'), 'D') + 1) + (LEVEL-1) * 7 + 6 last day of the week from dual connect by level <= 53 -- select level weekly according to ORACLE standard (WW), TO_DATE ('2017-01-01 ', 'yyyy-MM-DD ') + (LEVEL-1) * 7 day of week, TO_DATE ('2017-01-01', 'yyyy-MM-DD ') + (LEVEL-1) * 7 + DECODE (TO_CHAR (TO_DATE ('1970-12-31 ', 'yyyy-MM-DD'), 'ddd ')-(LEVEL-1) * 7), 2013, 2, 1, 6) from dual connect by level on the last day of the week <= 53

4. Obtain the largest week of the year.

-- Obtain the largest weekly (IW) calendar week in a year with params as (SELECT '000000' as nf from dual) SELECT TO_CHAR (TO_DATE (PM. NF | '-12-28', 'yyyy-MM-DD '), 'yyyiw') from dual left join params pm on 1 = 1

The preceding SQL statement uses the card to determine the week of June 1 of each year, and also determines the number of calendar weeks in a year.

5 Special notes

It is best to take the year with the week, because the week is the week relative to the Year, the second reason is that when IW is used, the same number of weeks may be generated for the start and end days of the year, and the first week of the year is unclear.

In oracle, I provide a week number. What function can be used to retrieve the start date and end date of this week?

Select SUNDAY, SATURDAY from
(Select
Sunday. the_week, decode (sign (Sunday. the_day-saturday.the_day),-1, Sunday. the_day, Sunday. the_day-7)
Sunday, Saturday. the_day Saturday from
(Select to_char (wwm, 'ww ') the_week, to_char (wwm, 'D') the_daynum, wwm the_day from (select
Trunc (sysdate, 'mm') + rownum-1 as wwm from user_objects where rownum <366) where
To_char (wwm, 'D') = 1) Sunday,
(Select to_char (wwm, 'ww ') the_week, to_char (wwm, 'D') the_daynum, wwm the_day from (select
Trunc (sysdate, 'mm') + rownum-1 as wwm from user_objects where rownum <366) where
To_char (wwm, 'D') = 7) Saturday
Where Sunday. the_week = Saturday. the_week)
Where the_week = 32

In ORACLE, how does one determine the start and end dates of a week? (Specific usage)

TRUNC is performed on the date and 'w' is used to intercept the data.
That is, the first day of the week.

The following is an example of the Year, quarter, month, week, date, and hour truncation.

The SQL Execution time is more than on

SQL> SELECT 'Year' AS truncType, TRUNC (SYSDATE, 'yyyy') FROM DUAL
2 UNION ALL
3 SELECT 'quarter 'AS truncType, TRUNC (SYSDATE, 'q') FROM DUAL
4 UNION ALL
5 SELECT 'month' AS truncType, TRUNC (SYSDATE, 'mm') FROM DUAL
6 UNION ALL
7 SELECT 'Week' AS truncType, TRUNC (SYSDATE, 'w') FROM DUAL
8 UNION ALL
9 SELECT 'day' AS truncType, TRUNC (SYSDATE, 'D') FROM DUAL
10 UNION ALL
11 SELECT 'hour' AS truncType, TRUNC (SYSDATE, 'hh ') from dual;

Trunctype trunc (SYSDATE, 'yyyy
---------------------------------
Year 00:00:00
Quarter 2010-10-01 00:00:00
Month 00:00:00
Week 2010-10-15 00:00:00
Day 2010-10-17 00:00:00
Hour 2010-10-17 22:00:00

6 rows selected.

With the start date, the end date is the start date + 6.