Number of weeks for Oracle fetch date __oracle

Preface

[Oracle] Date processing This article is for the "get the date of the Week" section of the above article unfolds.

Calculation criteria

Oracle supports two standards of time, one is Oracle's own standard, the other is ISO standard

1. Oralce Standard

1 every year January 1 as the first day of the year. (no matter what day of the week it is)

For example: 2013/01/01 is Tuesday, this day as the first day of 2013.

2 The calculation formula of week = Int (dayofyear+6)/7; DayOfYear is this day is the first days of the year

3) Weeks Range: 1-53
2. ISO Standard

1 every week is always starting from Monday, the end of Sunday

2 if January 1 is Friday, Saturday or Sunday, this week is the last week of the previous year, as most of the week is the year before

3 if January 1 is Monday, Tuesday, Wednesday or Thursday, this week is the first week of the new year, because most of this week is the year

4) time interval: 1-52 or 1-53

For example: For the 1998 and 1999 of January 1, 1998 is the first week, and the 1999 January 1 is counted as the last week of the previous year.

Table 3-7 The Week of the Year:example 1, January 1998

Mo	Tu	We	Th	Fr	Sa	Su	ISO Week
-	-	-	1	2	3	4	1 ISO Week of 1998
5	6	7	8	9	10	11	2 ISO Week of 1998
12	13	14	15	16	17	18	3 ISO Week of 1998
19	20	21st	22	23	24	25	4 ISO Week of 1998
26	27	28	29	30	31	-	5 ISO Week of 1998

Table 3-8 The Week of the Year:example 2, January 1999

mo	tu	we		fr	sa	su	iso Week
-	-	-	-	1	2	3	ISO Week of 1998
4	5	6	7	8	9	10	1 ISO Week of 1999
11	12	13	14	15	16	17	2 ISO Week of 1999
18	19	20	21st	22	23	24	3 ISO Week of 1999
25	26	27	28	29	30	31	4 ISO Week of 1999

acquired year in Oracle

1. Oralce Standard-YYYY

The way the Oralce standard gets the year is simple:

Select To_char (to_date (' 1997/01/01 ', ' yyyy/mm/dd '), ' YYYY ') from dual;
Select To_char (to_date (' 1997/12/31 ', ' yyyy/mm/dd '), ' YYYY ') from dual; 

Are all back to 1997.

2. ISO standard-IYYY

Select To_char (to_date (' 1997/01/01 ', ' yyyy/mm/dd '), ' iyyy ') from dual;
Select To_char (to_date (' 1997/12/31 ', ' yyyy/mm/dd '), ' iyyy ') from dual;

Return: 1997, 1998

Is it strange that the 1997/12/31 year was 1998.

In fact, the reason is very simple, according to the above criteria, January 1, 1998 is Thursday, then this week is the first week of the new year, because most of the week's time belongs to 1998. So 1997/12/31 also belonged to the first week of 1998, so it was 1998.

You might be thinking:

Is this because the string was converted to a date using YYYY-to_date (' 1997/12/31 ', ' yyyy/mm/dd '), if To_date (' 1997/12/31 ', ' iyyy/mm/dd ') is used, ' iyyy ' ) If you can. Unfortunately, Oracle does not support this usage.

Assuming that the day was 1997/12/31, using To_char (sysdate, ' iyyy '), it was found that the same was achieved by 1998.

To summarize: If you are converting from string to date, it is best to use YYYY if you are only taking the year of this date.

Oracle Get Week number

To get a day is the first week of the year, there are also two different criteria:

1. Oracle Standard-WW

This standard and algorithm looks silly. Int (dayofyear+6)/7

Select To_char (to_date (' 1997/01/01 ', ' yyyy/mm/dd '), ' YYWW ') from dual;
Select To_char (to_date (' 1997/01/08 ', ' yyyy/mm/dd '), ' YYWW ') from dual;
Select To_char (to_date (' 1997/12/31 ', ' yyyy/mm/dd '), ' YYWW ') from dual;

Very simple; Return 9701, 9702,9753

However, this standard should be less used in general companies.

2. ISO standard-IW

Select To_char (to_date (' 1997/01/01 ', ' yyyy/mm/dd '), ' IYIW ') from dual;
Select To_char (to_date (' 1997/12/31 ', ' yyyy/mm/dd '), ' IYIW ') from dual;

return: 9701,9801

As noted above, 1997/12/31 is the first week of 1998.

Special reminders are needed: (The essence of this article)

Years and weeks to use the same standard, do not mix,

For example: YYIW--Oracle's year, ISO number of weeks

Iyww

Otherwise, you will get some wrong results.

Select To_char (to_date (' 1997/12/31 ', ' yyyy/mm/dd '), ' YYIW ') from dual;

return: 9701

It's a whole year short.

And this is a mistake you may not find in a year or so, a year from now, the discovery is back to a year ago ^^