Oracle Date minute solution and week start end time calculation

1 ORACLE mid-week related knowledge description

1.1 Date formatting functions

To_char (X [,FORMAT]) : Press X toFORMATThe format is converted into a string. X is a date,FORMATis a format string that specifies the format in which X is converted.FORMATassociated with the week are W,WW,IW,D,fmww.

The meaning of W is the first week of one months. Returns the number of weeks in accordance with ORACLE 's custom standard weeks.

IWis the ISO Standard Week, and its meaning isISOStandard Week with Zhou Bie as the " Main Line ", can have a maximum of four weeks each year, but at least to include a weekly week, if the year of the week after the first If there is a greater than or equal to 4 days, then the first week of the year, otherwise the remaining days will be classified as the first 1 weeks of the next year; if less than 52 Zhou Bie Words, then the following year time to fill; fixed weekly starting from Monday as the first 1 days of the week, to Sunday as the first 7 days of this week; For example, in Oracle 2012 the year of the month is still the first 7 days of the week of the week of IW . This is used more.

WW is the ORACLE Custom Standard week, which means the first day of the first week of the year 1 months 1 days (regardless of the year 1 months 1 days is the day of the week ), For example: the year It was Wednesday and was defined in Oracle as the first day of the first week of the year WW . Rarely used in general.

D is the day of the week on which the current date is returned. is calculated from Sunday to Saturday, this is the place to pay attention to.

FMWW the Year 1 months 1 (regardless of the day of the week) start to the first week of the year, the second week from the beginning of the first week of the year (this is the same as the IW algorithm) The last week of the year with the year's month as of the deadline.

1.2 Date-time arithmetic functions

next_day (x, y) : used to calculate the time of the first week Y after X time . Y is a string that represents the full name of the day of the week represented in the current session language (such as Monday, Tuesday, and so on) or as a numeric value.

TRUNC (X [,FORMAT]) : Truncation Date , FORMAT in relation to the week there are D,IW,WW,W,fmww.

The meaning of W is the first week of one months. is a week number returned in accordance with ORACLE 's custom standard weeks.

IW is the ISO Standard Week, which returns the Monday of the week of the current date.

WW is a custom standard week for ORACLE . Returns the Monday of ORACLE custom Standard Week.

D means yes. returns the first day of the current week. It is surprising that according to D the first day of the current week is returned in Sunday. This is where we have to pay attention.

FMWW the Year 1 months 1 (regardless of the day of the week) start to the first week of the year, the second week from the beginning of the first week of the year (this is the same as the IW algorithm) The last week of the year with the year's month as of the deadline. The start time of the week is different from the IW when it crosses the year, for example, when you use FMWW in a year, 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]) : The rounding of the dates in FORMAT is related to the week Day . Round to the nearest Sunday from Monday to Wednesday and Thursday to Sunday.

2 take a week's start time and end time at a given time

-Take the start time and end time of the week
SELECT TRUNC (TO_DATE ('2014-07-18', 'YYYY-MM-DD'), 'IW') AS STARTDATE FROM DUAL;-Monday this week
SELECT TRUNC (TO_DATE ('2014-07-18', 'YYYY-MM-DD'), 'IW') + 6 AS ENDDATE FROM DUAL;-this Sunday

SELECT TRUNC (TO_DATE ('2014-07-18', 'YYYY-MM-DD'), 'IW')-7 AS STARTDATE FROM DUAL; --- Monday last week
SELECT TRUNC (TO_DATE ('2014-07-18', 'YYYY-MM-DD'), 'IW')-1 AS ENDDATE FROM DUAL;-Last Sunday

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

The difficulty of taking the start and end times of a natural week is to determine whether the days of the year are the first week of the year or the last week of the previous year, depending on IW the definition of natural week, less than equals 3 Day is the word of the year, which belongs to the last week of the previous year, greater than or equal to 4 The day is the first week of the year, plus the last days of the previous year.

-Start week and end week (IW) natural week according to the week from Monday to Sunday
WITH PARAMS AS (SELECT TRUNC (TO_DATE ('2009-01-01', 'YYYY-MM-DD'), 'YYYY') AS SD FROM DUAL)
SELECT LEVEL 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))
 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
Last day of the week
FROM DUAL D
LEFT JOIN PARAMS PM ON 1 = 1
CONNECT BY LEVEL <= 53
-Calculate the start and end time of the week according to the week from Sunday to Saturday (D)
SELECT LEVEL week, (TRUNC (TO_DATE ('2011-01-01', 'YYYY-MM-DD'), 'YYYY')-7) + (7-TO_CHAR (TRUNC (TO_DATE ('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'), 'YYYY')-7) + (7-TO_CHAR (TRUNC (TO_DATE ('2011-01-01', 'YYYY- MM-DD '),' YYYY '),' D ') + 1) + (LEVEL-1) * 7 + 6 the last day of the week
FROM DUAL CONNECT BY LEVEL <= 53
--According to ORACLE standard (WW)
SELECT LEVEL week, TO_DATE ('2013-01-01', 'YYYY-MM-DD') + (LEVEL-1) * 7 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) for the week The last day
FROM DUAL CONNECT BY LEVEL <= 53

4 get maximum weeks of a year

-Get the largest weekly (IW) Chinese calendar week in a year
WITH PARAMS AS (SELECT '2014' AS NF FROM DUAL)
SELECT TO_CHAR (TO_DATE (PM.NF || '-12-28', 'YYYY-MM-DD'), 'IYYYIW') FROM DUAL LEFT JOIN PARAMS PM ON 1 = 1 

above SQL through the card every year A Month - which week it belongs to, and how many natural weeks there are in a year.

5 particular place to be aware of

It is best to take the year with you when you take the week, first because the week is relative to the week of the year, and the second because when using IW At the beginning of the year and the end of the day may produce the same number of weeks, it is unclear which year the first week.

Oracle Date minute solution and week start end time calculation