Oracle Date detailed solution and weekly start end time calculation ____oracle

1 Oracle Middle-Week related knowledge description

1.1 Date Format function

to_char (x [, Format]): Converts X to a string as format. X is a date, format is a formatted string that sets the format for which X is converted, and the format is W,WW,IW,D,FMWW with week-related.

The meaning of W is the week ordinal of one months. is to return the week number according to the Oracle custom standard weeks.

IW is an ISO standard week, it means that the ISO standard week is Zhou Bie as the "mainline", with a maximum of 53 weeks per year, but at least 52 weeks per year, and if there are greater than or equal to 4 days between the 52nd Zhou Bie of the year and the December 31 of that year, Is the 53rd week of the year, otherwise, the remaining days are classified as the 1th week of the next year, and if less than 52 weeks, the following year is filled; fixed weekly starting from Monday as the 1th day of the week, to Sunday as the 7th day of the week; for example: in Oracle January 01, 2012 still belongs to the 7th day of the 52nd week of IW Week 2011. This is a lot to use.

WW is the standard week for Oracle customization, which means January 1 of the year as the first day of the first week of the year (regardless of the January 1 of the year), for example: 2014 01 01 is Wednesday, the first day of the first week in Oracle, which is defined as the 2014 WW. Usually rarely used.

D is to return the current date is the day ordinal of this week. is calculated from Sunday to Saturday, and this is the place to pay attention to.

FMWW The beginning of the year, January 1 (regardless of the number of days of the week) to the first week of the year, the second week is the beginning of the first week of the year (this is the same as the IW algorithm) the last week of the year is due to December 31 of the year.

1.2 Date-time operation function

next_day (x,y): A time for calculating the first week of Y after X time. Y is a string representing the full name of the day of the week in the current session language (such as Monday, Tuesday, etc.), or a numeric value.

TRUNC (X [, FORMAT]): truncate date, in FORMAT with week-related D,IW,WW,W,FMWW.

The meaning of W is the week ordinal of one months. is to return a week number according to the Oracle custom standard weeks.

IW is the ISO standard week, which returns the Monday of the week on which the current date is located.

WW is the standard week for Oracle customization. Returns the Monday of the Oracle Custom Standard week.

The meaning of D is to return to the first day of the current week. It is strange that the first day of the current week is returned according to D, which is Sunday. This is where we should pay attention.

FMWW The beginning of the year, January 1 (regardless of the number of days of the week) to the first week of the year, the second week is the beginning of the first week of the year (this is the same as the IW algorithm) the last week of the year is due to December 31 of the year. The start time of the week is somewhat different from the IW in the year of 2012, for example, when the FMWW is used in 01 01, the start time of the week is 2012-01-01, when using IW, the start time of the week is 2011/12/26.

ROUND (X [, FORMAT]): Day in the rounded FORMAT of the date that is related to the week. It is rounded to the nearest Sunday from Monday to Wednesday and Thursday to Sunday.

2 start and end times of a week based on a given time

--Start time and end time of the week
Select TRUNC (to_date (' 2014-07-18 ', ' yyyy-mm-dd '), ' IW ') as StartDate from dual;--this week Monday
Select TRUNC (to_date (' 2014-07-18 ', ' yyyy-mm-dd '), ' IW ') + 6 as EndDate from dual;--this week Sunday

SELECT TRUNC (' 2014-07-18 ', ' Yyyy-mm-dd '), ' IW ')-7 as StartDate from DUAL;---last Monday   
SELECT TRUNC (to_date (' 2014-07-18 ', ' yyyy-mm-dd '), ' IW ')-1 As  EndDate from dual;--last Sunday

3 start and end times of a week based on a given number of weeks

The difficulty of taking the start time and the end time of the natural week is to judge that the days of the year are the first week of the year, or the last week of the previous year, according to the definition of IW natural week, less than or equal to 3 days is this year, the last week of the previous year, greater than 4 days belong to this year's words, plus the last few days of the year, Calculate the first week of the cost year.

--the start and end times of the week from Monday to Sunday (IW) Natural Week with PARAMS as (SELECT TRUNC (to_date (' 2009-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)) when the first day of the week, DECODE (SIGN 5-decode (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 when the last day of the week from the DUAL D left JOIN PARAMS PM on 1=1 CONNECT by level<=53-the start and end times of the week from Sunday to Saturday (d) SELECT level Weekly, (TRUNC (to_date (' 2011-01-01 ', ' yyyy-mm-dd '), ' YYYY ') -7) + (7-to_char TRUNC (' 2011-01-01 ', ' Yyyy-mm-dd '), ' YYYY '), ' D ') +1 + (LEVEL-1) *7 The first day of the Week (TRUNC (' to_date ', ' 2011-01-01 '), ' yyyy-mm-dd ') -7) + (YYYY ( TRUNC (to_date (' 2011-01-01 ', ' yyyy-mm-dd '), ' YYYY '), ' D ') (+1) + (LEVEL-1) *7+6 when the last day of the week from DUAL CONNECT by level<=53-- According to Oracle Standard (WW) SELECT level Weekly, To_date (' 2013-01-01 ', ' yyyy-mm-dd ') + (LEVEL-1) *7 when the first day of the week, To_date (' 2013-01-01 ', ' Yyyy-mm-dd ') + (LEVEL-1) *7+
    DECODE (To_char (to_date (' 2013-12-31 ', ' yyyy-mm-dd '), ' DDD ')-(LEVEL-1) *7), 1,0,2,1,6) when the last day of the week from DUAL CONNECT by level&
 lt;=53

4 Get the maximum weekly time of the year

--Get the largest weekly (IW) China calendar Natural Week with PARAMS as
(SELECT ' 2014 ' as NF from DUAL)
select To_char (PM. NF | | ' -12-28 ', ' yyyy-mm-dd '), ' Iyyyiw ' from DUAL left JOIN PARAMS PM on 1=1

The above SQL passes through the card each year of December 28 which week, also determines how many natural weeks a year.

5 places of special attention

It's best to take the year with you at other times of the week. The first is because weeks are relative to the first few weeks of the year, and second, because when using IW the beginning of a year and the end of those days may produce the same number of weeks, it is not clear which year of the week.